# Countif or Sumif with multiple criteria

1. ## Countif or Sumif with multiple criteria

Hi Guys,

I know this is simple but I just can't manage to crack a formula for this one.

I need to count all the rows that satisfies 2 criteria:
1.) Column D must have "VVV"
2.) Column I should be "XXX"

I need the answer to be reflected in a single cell. In short can you combo a formula using Countif or Sumif?  Register To Reply

2. Try,

Sumif+Sumif

Countif+Countif  Register To Reply

3. Sir,

I think it will add all of those with
1.) Column D must have "VVV"
or
2.) Column I should be "XXX"

i actually need the formula to count only if
1.) Column D must have "VVV"
and
2.) Column I should be "XXX"
are both satisfied.

any ideas?  Register To Reply

4. Try this

=COUNTIF(D1:D5,"VVV")+COUNTIF(I1:I5,"XXX")

or do you mean you only want to count column D cells where the same row in column I ="XXX"  Register To Reply

5. it actually adds all of the cells which complies either 1 on the 2 criteria. I was hoping to count it only if it will comply to both.

.................Column D.......Column I
Dont Count....ABC.......................
Dont Count.......................VVV....

COUNT..........ABC..............VVV.... this is the formula that i need to figure out.

I hope you can help me with this.  Register To Reply

6. The way to use sumif or countif with multiple criteria is to use an array formula or multiple cell formulas.

Let me explain the multiple cell formulas first so you will get the idea.

1.) Column D must have "VVV"
2.) Column I should be "XXX"
In column B (or any blank column) put the formula:

=(D1="VVV")*(I1="XXX")

Then, fill down this column as far as you need to. You will see 0 if either condition is false and 1 only if both conditions are true.

To get the count where both are true, sum the column in which you wrote the functions.  Register To Reply

7. ## Continuing my previous posting

Now, to get the job done without using multiple columns to do it, you would use something like this:

{=(D1:D20="VVV")*(I1:I20="XXX")}

You DO NOT type the {} yourself. You type the formula without the {}, then instead of pressing Enter when you finish, you press Ctrl+Shift+Enter. Excel understands this key combination as defining an array formula, and it enters the {} for you.

Caveat: You CANNOT use an entire column for an array formula. So, you need to be selective. You CAN use a NAME in place of the cell addresses.

Hope this helps.  Register To Reply

8. Originally Posted by Eladamri
Hi Guys,

I know this is simple but I just can't manage to crack a formula for this one.

I need to count all the rows that satisfies 2 criteria:
1.) Column D must have "VVV"
2.) Column I should be "XXX"

I need the answer to be reflected in a single cell. In short can you combo a formula using Countif or Sumif?
To count...

=SUMPRODUCT(--(\$D\$2:\$D\$100="VVV"),--(\$I\$2:\$I\$100="XXX"))

To sum...

=SUMPRODUCT(--(\$D\$2:\$D\$100="VVV"),--(\$I\$2:\$I\$100="XXX"),\$J\$2:\$J\$100)

...where the corresponding values in Column J are summed.

Hope this helps!  Register To Reply

9. That is a better solution than mine.  Register To Reply

10. I understand that the following code works for the above situation.

=SUMPRODUCT((A1:A19="ABC")*(B1:B19="VVV"))

where in A Column, ABC is to be checked and in the B Column, VV is to be checked...

But just wndering that why the Following code is not working??? Can you please suggest what I am doing wrong here:

{=SUM(IF(AND(A1:A19="ABC",B1:B19="VVV"),1,0))}

Any thoughts?  Register To Reply

11. {=SUM(IF(AND(A1:A19="ABC",B1:B19="VVV"),1,0))}

What this say's is, if all cells in A1:A19="ABC" and all cells in B1:B19="VVV" that equals 1, otherwise it's equal to 0, then SUM 1 or 0  Register To Reply

12. Originally Posted by oldchippy
{=SUM(IF(AND(A1:A19="ABC",B1:B19="VVV"),1,0))}

What this say's is, if all cells in A1:A19="ABC" and all cells in B1:B19="VVV" that equals 1, otherwise it's equal to 0, then SUM 1 or 0

Awesome, thnx so much!!!! One more thing..if we don't include {}, then what will be the difference??  Register To Reply

13. You will get an error if you do not enter it as an Array - Ctrl+Shift+Enter

Take a look at this link for more explanation

http://www.cpearson.com/excel/array.htm  Register To Reply

14. I thnk that link is down...any other links???   Register To Reply

15.  Register To Reply

16. Originally Posted by oldchippy
Cool, thnx for the info!!! I found them nice!!!

I have put thm on my Blog too...I hope you wont mind!!!

Can you please goto http://excelnoob.blogspot.com and verifies if thats ok to post ur links there???  Register To Reply

17. Yep - great blog - thanks for the mention fame at last!  Register To Reply