CODEWITHSUNDEEP
excelexcel-formula
ap603
ap603

Reputation: 3

Excel - counting people that meet a criterion and have one or another value

I have a large dataset that is similar to the below:

A   B   C   D  
1   X  .5   0   
2   X   0   0  
3   Y  .5   1   
4   X   1  .5

I would like to count the number of instances for which column B = X and the value in column C or D is greater than 0.

So for the above case the answer would be 2.

As this count is part of a larger output table, I'd like to not use a pivot.

I've tried fumbling around with countifs and frequency to no avail.

Thanks for any help!

Upvotes: 0

Views: 49

Answers (2)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60379

You could try:

=SUMPRODUCT((B="X")*(((C>0)+(D>0))>0))

Edit: Explanation In this kind of formula, you can consider that multiplication does an AND operation, and addition does an OR operation. Of course, with the OR operation, since both C and D could be > 0, you need to test that summation to ensure that you are not double counting.

Upvotes: 1

user4039065
user4039065

Reputation:

There are three possible outcomes that can produce an accurate total count when summed together.

=SUM(COUNTIFS(B2:B5, "X", C2:C5, ">0", D2:D5, "<=0"),
     COUNTIFS(B2:B5, "X", C2:C5, "<=0", D2:D5, ">0"),
     COUNTIFS(B2:B5, "X", C2:C5, ">0", D2:D5, ">0"))

Upvotes: 1

Related Questions