Double Countif
 

Hi all. I need to do a 2 level countif. Given the following 3 column
example:

A 1 0
B 0 0
A 1 0
C 1 1
D 0 1
A 0 0
X 0 0

I need a formula at the bottom of each column that will count the number of
entries that are BOTH greater than zero AND have A in the forst column. So
the first numerical column result would be 2, the second would be 0.
Possible?

Double Countif
 

One way:

B8: =SUMPRODUCT(--($A1:$A7="A"),--(B1:B70))
C8: =SUMPRODUCT(--($A1:$A7="A"),--(C1:C70))

If B and C can only have values of 0 or 1, this simplifies to

B8: =SUMIF($A1:$A7,"A",B1:B7)
C8: =SUMIF($A1:$A7,"A",C1:C7)





In article ,
"Steph" wrote:

Hi all. I need to do a 2 level countif. Given the following 3 column
example:

A 1 0
B 0 0
A 1 0
C 1 1
D 0 1
A 0 0
X 0 0

I need a formula at the bottom of each column that will count the number of
entries that are BOTH greater than zero AND have A in the forst column. So
the first numerical column result would be 2, the second would be 0.
Possible?

Double Countif
 

Wow, fantastic! Can I ask you to explain how that works? The -- really has
me baffled!

"JE McGimpsey" wrote in message
...
One way:

B8: =SUMPRODUCT(--($A1:$A7="A"),--(B1:B70))
C8: =SUMPRODUCT(--($A1:$A7="A"),--(C1:C70))

If B and C can only have values of 0 or 1, this simplifies to

B8: =SUMIF($A1:$A7,"A",B1:B7)
C8: =SUMIF($A1:$A7,"A",C1:C7)





In article ,
"Steph" wrote:

Hi all. I need to do a 2 level countif. Given the following 3 column
example:

A 1 0
B 0 0
A 1 0
C 1 1
D 0 1
A 0 0
X 0 0

I need a formula at the bottom of each column that will count the number
of
entries that are BOTH greater than zero AND have A in the forst column.
So
the first numerical column result would be 2, the second would be 0.
Possible?

Double Countif
 

See

http://www.mcgimpsey.com/excel/doubleneg.html


In article ,
"Steph" wrote:

Wow, fantastic! Can I ask you to explain how that works? The -- really has
me baffled!

Double Countif
 

Thank you!!

"JE McGimpsey" wrote in message
...
See

http://www.mcgimpsey.com/excel/doubleneg.html


In article ,
"Steph" wrote:

Wow, fantastic! Can I ask you to explain how that works? The -- really
has
me baffled!