Count rows where a specific value appears in any of 4 columns

  • Thread starter Thread starter LisaM
  • Start date Start date
L

LisaM

This is an example of the spreadsheet I'm working with:

A B C D
1 1 1 1
0 0 0 1
0 0 0 0
0 0 0 10
0 0 0 10
0 0 0 0
4 4 1 1
0 0 0 0
0 0 1 1
0 0 0 0
1 1 0 0

What I would like to do is count the number of rows where the value 1 occurs
in the four columns, wherever that might be.

The answer for the data above, for example, would be 5 (five rows contain
the value 1 somewhere in the four columns).

There are 5,807 rows of data in the worksheet and I'm using Excel 2003.

Thank you.
 
Lisa
Try:
=SUM(IF((A2:A5807=1)*(B2:B5807=1)*(C2:C5807=1),D2:D5807))
The formula must be entered as array formula - press CTRL+SHIFT+ENTER
Hope this helps
 
Hi Ron@Buy

I tried the formula and got a value which I can't check to see if it's
correct (because of the sheer number of rows) but I tried it for eight rows
of data and the value returned for those eight rows was incorrect.

Could you run me through the logic behind the formula?

Thank you!
 
Hi,

In column E, use the formula COUNTIF(A2:D2,1)>0 and copy all the way down to
row 5807. Now in a spare cell, use the formula =COUNTIF(E2:E5807,TRUE)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
 
Back
Top