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

Clubbing multiple "Countif" with multiple criteria in a single "Countifs"

0

Hi,

I'm trying to prepare a dashboard using countifs to know how many cells would meet the criteria I have mentioned. Please, go through the excel file to find the formulas I have used. It is self-explainatory. The drop-down table has induvidual drop-downs against the Role, Shift & Status (in F7, G7 & H7); whose values would be used as criteria for my countif function. As you can see, induvidually taken, these countif functions with multiple criteria works fine and returns results in F8, G8 & H8.

Normally, One would use countifs function to club multiple countif functions. But in my case, it would not return the correct value. Kindly help me to understand what can I do to return the correct value in cell F14.

P.S. I have tried using pivots and slicers to get the result. But I need this exact method to implement it in further reports. Thanks in Advance.

Answer
Discuss

Answers

0

Hi, not sure why you've used an array formula in F14 (as evidenced by bounding curly brackets) but you look in cell F16 (in my revised file) you'll see a regular but corrected (three criteria) COUNTIFS formula as follows:

=COUNTIFS(A2:A44,$F$7,B2:B44,$G$7,C2:C44,$H$7)

(It checks per row if cell A matches F7 and if cell B matches G7 and if cell C matches H7).

I've set your drop downs to detect the unique "YC" role and F16 shows 1 but if you change Shift or Status, it will go to 0 (since there are no matches). I tried it also with FC, Night, Present and it returned 3 correctly).

That seems to work well (so you might no need to display F8, G8 and H8 which matches to individual* criteria. (* note the spelling!)

Hope this helps

Discuss


Answer the Question

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