Excel CountIf with Multiple Nested If Statements

Question

I have a table of Data in Excel on one tab that I have created a report for on another tab. The Report is a table filled with calculations which are fed from the data tab. Right now all results are shown for the week ending date I select. I am now trying to add code to the formula calculations that allows me to further progressively filter the table results.

For example From:

"All" Default Data for selected Week Ending

-->Filter by column F in data tab
--> Filter by column F & G, but if F is blank show all values in column  F and filter only on G
--> Filter by column F, G and H, but if F or G is blank filter only on column G- and for any combination of blanks and selected values there should be.

I have the extra filters set up as drop down selections from the data validation tab and the lists are all in a 3rd tab away from the report and master data. I have 8 columns right now that I am trying to be able to filter on in any combination.

This is an example of the code I started with for the filter:

(1) =IF($Q$4="",COUNTIFS(Master!A:A,B$2),COUNTIFS(Master!$A:$A,B$2,Master!B:B,$Q$4))

(2)=IF($Q$5="",COUNTIFS(Master!$A:$A,B$2),COUNTIFS(Master!$A:$A,B$2,Master!C:C,$Q$5))

Breakdown of Formula 1 (same concept for the remaining):

Q4 refers to the cell that the dropdown list resides for this particular filter Master!A:A is the master data file B2 is the Date being searched for in the Master Data Master!B:B is the column to be filtered by the Q4 selection

So far when trying to combine two or more of the Q selections (there are several, Q4,Q5,Q6,Q7,Q8,Q9,Q10,Q11 which filters columns B:B,C:C,D:D,E:E and so on in the master data. I need the table formulas to be able to handle receiving input from the dropdown and progressively filter down the data for each selection, or allow all data from that column (not already filtered out) to pass through to the next criteria.

So far when I try to combine the Q dropdown formulas I end up with a value of 0, since even if I have a

    countifs(if(OR(Q2="", Q3="") followed by the filter criteria

if one of the filters is not selected, the formula drops the total value to "0" when it gets to the blank in the Q criteria.

Someone please help! Is there another way to do this? I feel like I am so close and am just missing something small.

TIA,

Jesse

Scott Craner Scott Craner · Accepted Answer · 2017-12-06T19:53:47

So something like this:

=COUNTIFS(Master!$A:$A,B$2,Master!B:B,IF($Q$4<>"",$Q$4,"*"),Master!c:C,IF($Q$5<>"",$Q$5,"*"),...)

Excel CountIf with Multiple Nested If Statements

1 Answers