# 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?

2. Try,

Sumif+Sumif

Countif+Countif

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?

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"

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.

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.

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.

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!

9. That is a better solution than mine.

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?

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

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??

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

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

15. 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???

16. Yep - great blog - thanks for the mention fame at last!

#### Thread Information

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1