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

[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.

 

[Ron@Buy]

Lisa
Try:
=SUM(IF((A2:A5807=1)*(B2:B5807=1)*(C2:C5807=1),D25807))
The formula must be entered as array formula - press CTRL+SHIFT+ENTER
Hope this helps

 

[LisaM]

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!

 

[Ashish Mathur]

Hi,

In column E, use the formula COUNTIF(A22,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

 

[LisaM]

Hi Ashish,

Thank you for your help. This does this job!

Lisa

 

[Ashish Mathur]

You are welcome.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com