Help with stacked if / countif formulas with multiple dropdown criteria

GP2016

New Member
Joined
Mar 3, 2016
Messages
3
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 AreaAll
Key only?Y
Risk levelM
TypeD
ClassITDM

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

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

FDibbins

Well-known Member
Joined
Feb 16, 2013
Messages
6,723
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)
 

GP2016

New Member
Joined
Mar 3, 2016
Messages
3
The shortening formula does not work. Is it against the rule to share my spreadsheet via Dropbox?... Checking...
 

GP2016

New Member
Joined
Mar 3, 2016
Messages
3
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,228
Messages
5,594,934
Members
413,953
Latest member
Arthur1471

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top