# Help with stacked if / countif formulas with multiple dropdown criteria

#### GP2016

##### New Member
I am creating a summary, where I want to count a number of occurences (e.g., Pass, Fail, etc.) according to multiple combinations of criteria in other columns. My problem is with getting the multiple combinations. My formula is getting monstrous and by my logic this might go a lot longer.

Here's what it currently looks like...

=IF(AND(\$AE\$13="All",\$AE\$14="All",\$AE\$15="All",\$AE\$16="All",\$AE\$17="All"),COUNTIF(BU!\$V\$6:\$V\$14,'Summary - Results'!C\$11),

IF(\$AE\$13="All",COUNTIFS(BU!\$V\$6:\$V\$14,'Summary - Results'!C\$11,BU!\$H\$6:\$H\$14,'Summary - Results'!\$AE\$14,BU!\$I\$6:\$I\$14,'Summary - Results'!\$AE\$15,BU!\$F\$6:\$F\$14,'Summary - Results'!\$AE\$16,BU!\$G\$6:\$G\$14,'Summary - Results'!\$AE\$17),

COUNTIFS(BU!\$V\$6:\$V\$14,'Summary - Results'!C\$11,BU!\$H\$6:\$H\$14,'Summary - Results'!\$AE\$14,BU!\$B\$6:\$B\$14,'Summary - Results'!\$AE\$13,BU!\$I\$6:\$I\$14,'Summary - Results'!\$AE\$15,BU!\$F\$6:\$F\$14,'Summary - Results'!\$AE\$16,BU!\$G\$6:\$G\$14,'Summary - Results'!\$AE\$17)))

These are the criteria I have.

 Financial Area All Key only? Y Risk level M Type D Class ITDM

<tbody>
</tbody>

My formula gets me to the point I can change the Financial Area to whatever I want from my source data, BUT the other criteria must be defined (i.e., cannot be All). In essence, I want the results with all, without any AND with a combination of criteria/filters.

Any help?

### Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
I am trying to figure out what you want here.
What does your data look like?
Do any of those formulas work/not work?
If they don't work, what are you expecting and what are you getting?
For counting on multiple criteria, COUNTIFS is generally what you would use, and it is pretty straight forward...criteria-range, criteria etc

That 1st formula...
=IF(AND(\$AE\$13="All",\$AE\$14="All",\$AE\$15="All",\$AE\$16="All",\$AE\$17="All"),COUNTIF(BU!\$V\$6:\$V\$14,'Summary - Results'!C\$11),
can be shortened to this...
=IF(countif((\$AE\$13:\$AE\$17,"ALL")=5,COUNTIF(BU!\$V\$6:\$V\$14,'Summary - Results'!C\$11)

The shortening formula does not work. Is it against the rule to share my spreadsheet via Dropbox?... Checking...

So here it is... https://www.dropbox.com/s/ksjnnc8xkc2ijkm/Help with formula.xlsx?dl=0

As it stands, my formula does work, but is incomplete.

The 3rd line of the formula would work fine also if all 5 of my criteria were well defined (i.e., selected from data within my source table / same as applying no filters).

I added the second line so I could apply or remove filters only for the 1st criteria. Best of both worlds. You might I had to remove the 'All' condition from it, when you compare to the 3rd line.

So now my challenge is being able to remove the filters (i.e., use 'All') for the remaining 4 criteria. I don't know if I'll get there following my logic.

Replies
1
Views
149
Replies
1
Views
306
Replies
6
Views
338
Replies
5
Views
329
Replies
1
Views
321

1,221,523
Messages
6,160,321
Members
451,637
Latest member
hvp2262

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back