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

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.

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

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

"LisaM" wrote:

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.

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

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!


"Ron@Buy" wrote:

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

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

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

"LisaM" wrote in message
...
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.

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

Hi Ashish,

Thank you for your help. This does this job!

Lisa

"Ashish Mathur" wrote:

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

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

You are welcome.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"LisaM" wrote in message
...
Hi Ashish,

Thank you for your help. This does this job!

Lisa

"Ashish Mathur" wrote:

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