# Thread: countif with two criteria? (2000 sr1)

1. ## countif with two criteria? (2000 sr1)

I need to count the entries in column b that are over 0 (not negatives or 0) and where the data in the same row in column a is equal to "E".
For example;
A B
a -4
b 7
e 6
e -9
a 7
Would give me the answer 6. I have tried a host of combinations with Countif, If, and the like array entered and I cannot get around this problem. Help!

2. ## Re: countif with two criteria? (2000 sr1)

Neil, if you really meant count in the Excel sense then the answer is 1 and the formula is {=COUNT(IF((B1:B5>0)*(A1:A5="e"),1,""))}. However, if you meant sum, then the answer is 6 and the formula is {=SUM(IF((B1:B5>0)*(A1:A5="e"),B1:B5,""))}
In both cases, the {} are entered by Excel, not you, because these are array formulas and are entered with <Ctrl><Shift><Enter>. HTH --Sam

3. ## Re: countif with two criteria? (2000 sr1)

Appologies, I did mean count, and typing it in a hurry put 6 instead of 1. Thanks anyway.

4. ## Re: countif with two criteria? (2000 sr1)

Another problem for you. What if I wanted to sumproduct on columns b and c but only where column A was = "E"?

5. ## Re: countif with two criteria? (2000 sr1)

Hi Neil,
Try something like:
=sum((A1:A5="E")*(B1:B5)*(C1:C5))
Hope that helps.

6. ## Re: countif with two criteria? (2000 sr1)

This includes cells where the data in C is a negative. Just to make things harder, I only want to sumproduct where data in column A is = "E" and data in column c is >0.

Thanks for the help.

7. ## Re: countif with two criteria? (2000 sr1)

Neil, in your original example there was no column C so I'm a tad confused, but does

{=SUM((column_A_range>0)*(column_B_range="e")*(col umn_A_range))}

array entered, do what you want or get you in the right direction?

8. ## Re: countif with two criteria? (2000 sr1)

Oh OK. Then either:
=sum((A1:A5="E")*(B1:B5)*(C1:C5>0)*(C1:C5))
or
=sum((A1:A5="E")*(B1:B5)*if(C1:C5>0,C1:C5,0))
both array-entered should do it.
Hope that helps.

9. ## Re: countif with two criteria? (2000 sr1)

<img src=/w3timages/blackline.gif width=33% height=2>
> sumproduct on columns b and c but only where column A was = "E"?
<img src=/w3timages/blackline.gif width=33% height=2>

Assuming you added a column of numbers in C1:C5:<pre>{=SUMPRODUCT(IF(A1:A5="e",B1:B5,0),IF(A 1:A5="e",C1:C5,0))}</pre>

entered w/o the braces as an array formula.

Thanks

11. ## Re: countif with two criteria? (2000 sr1)

{=SUMPRODUCT(IF(A1:A7="e",B1:B7,0),IF(B1:B7>0,C1:C 7,0))} array entered is the idea I was after. This anwser came from SammyB and I just adjusted it to suit my needs. Sorry about any confusion but I was amending what I wanted to do on the fly.

Cheers

12. ## Re: countif with two criteria? (2000 sr1)

I won't crticize the solutions presented which are probably correct. However, I personally find them rather complicated in spite of the fact that I have been using Excel for too many years to remember. I often wonder why people don't create an extra column in this case with the appropriate logic to calculate the impact on one cell. Then you can copy that logic to all the cells in the column and perhaps sum the total at the bottom. Perhaps it will create a larger file but the logic is eminently easier to follow and probably correct. In addition, the process is usually easier to scale up to more difficult problems.

13. ## Re: countif with two criteria? (2000 sr1)

Amen, I wrote them & I hate them. I would aviod an array formula at all costs. --Sam

14. ## Re: countif with two criteria? (2000 sr1)

To each his own. We are all guilty of excessive spreadsheet elegance at some time.

On one hand I prefer the simplest solution (Occam's razor) but I have a strong preference for single cell formulas for layout reasons. I get too much multicolumn true mean calculations from people who don't use =SUMPRODUCT as it is; using extra cells when it is isn't necessary can obscure the intent and focus of the analysis.

But I also think you should consider one other reason to use array formulas in the context used here; they are much easier than setting up an equivalent =DSUM(,,) formula, which is the only other way to do complex criteria formulas.

15. ## Re: countif with two criteria? (2000 sr1)

I partially agree with you but it is not always possible to put in that extra column. It depends what is going on with the rest of the data and other automated tasks already set in motion. However, a good point and certainly one worth thinking about when planning new spreadsheets.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•