Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Calculating several data group with multiple criteria

0

Hello, i need your help to calculate some group of data with combined criteria.

As you see in the Excel file i got error there using countifs to count the total of Type 1 & 2 in each continent. I dont understand why the formula gave all results 0 instead of 3 in Africa continent, 4 in America continent and so on.

Does the countifs function not fit for what im doing? or there's a wrong syntax in my formula?

I'm waiting your reply, thanks in advance.

Answer
Discuss

Answers

0
Selected Answer

Fida

The COUNTIFS function will work for this but remember that it gives a 1 every time all criteria pairs are met- your formula in H2:

=COUNTIFS($D:$D,"Type 1",$C:$C,"Africa",$D:$D,"Type 2",$C:$C,"Africa")
needs cells in column C to be "Africa" but in column D to be both "Type 1" and "Type 2" (so you get 0 since D is either Type 1 or 2).

You need to add two COUNTIFS like this (for H2 again) like this:

=COUNTIFS($D:$D,"Type 1", $C:$C,"Africa") +  COUNTIFS($D:$D,"Type 2", $C:$C,"Africa")
so you count the matches for Africa and Type 1 first and then again but for Type 2.

In the attached file, I've done the formulae for H2:K2.

Note that you can have multiple criteria per column (which will AND togther for the puposes of counting). For example in column 0 of the attached file, you'll see some numbers in column O and in cell O14, the formula:

=COUNTIFS(O3:O12, ">=" & 7,O3:O12, "<=" & 14)
allows us to count how many numbers are between 7 and 14 say. Note the bits in bold above (where arithmetic operators are within inverted commas) - when you're in cell 014, use the fx button to left of the formula bar) to get help on the syntax of the function via the "Help on this function" hyperlink.

Hope this is clear.

Discuss

Discussion

Thanks alot John for the reply, the formula works and very helpful.
So, the Countifs function can only count combined criteria but only one criteria in each column right? and if I want to count more than one criteria in one column again next time i should use add the other countifs then 
Fida_mutia (rep: 28) Jan 19, '22 at 7:47 pm
Thanks for selecting my Answer, Fida   Please see the revised Answer/ file- I've given an example of multiple criteria for a column. Take care since you can't use them simply in cases with mutually exclusive possible entries, like your Type 1 Type 2 etc.
John_Ru (rep: 6102) Jan 20, '22 at 3:32 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login