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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

What is the "--" (double dash) in MS Excel formula? 2
Excel VBA 1
If, VLookup, Index, or a combination of all of them? 7
OR in SUMPRODUCT formula 3
Counting occurance of >0 in two columns 3
Excel Need Countifs Formula Help 0
Find values in table and return row/column name 7
calculating subtotals for sequential blank cells in 1 column 1

Back
Top