COUNTIFs: Multiple Cynamic Criteria

[Beginner041699]

I am struggling with a COUNTIF formula. I previously achieved success but now I want to add the option of choosing criteria so it is dynamic. Best explained, I need to be able to Count the number of times: "CEO-CFO"; "President"; "Manager" appear in each question (5 separate questions - so 5 different counts). But then I also want the count to change based on the criteria chosen. I will choose from a drop down menu the criteria, the criteria is coming from different columns in a table in another worksheet or I simply can choose All and I want the total count.

On the Calculation tab: In E2:E11 is the criteria to be chosen. B17:D21 is where I am trying to complete the COUNT.
Data: CRITERIA LOOK-UP for 'Calculation'!E2:E11 is in the tab 'Data'!C; 'Data'!S; 'Data'!D; 'Data'!H; 'Data'!C;'Data'!R;'Data'!T and Questions for the COUNT in Columns J:N


I have tried several things: COUNTIFs; SUM IFS and I cannot get anything to work.

File is attached

Any help is greatly appreciated!

[Glenn Kennedy]

No file. please have another go...

[Glenn Kennedy]

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[AliGW]

Welcome to the forum.

Your file is not yet attached. Let us know if you are having problems.

[Beginner041699]

I was able to attach file (finally) but I had to change a lot due to size of file.

On the Calculation tab: In E2:E5 is the criteria to be chosen. B10:D12 is where I am trying to complete the COUNT.
Data: CRITERIA LOOK-UP for 'Calculation'!E2:E7 is in the tab 'Data'!B; 'Data'!H; 'Data'!J and Questions for the COUNT in Columns D:F

[Beginner041699]

UPDATE: I have a formula that is working BUT...I have too many scenarios for Excel to accept. How do I shorten the formula so I can get all possible outcomes to COUNTIFs...I am only showing 3 different criteria with each having 2 options but my real data set will have 5 criteria with each having 2 options. So close but just need to get over this o. =IF(AND(E5="Yes",E6="Yes"),COUNTIFS(DATA!D:D,CALCULATION!B10,DATA!J:J,CALCULATION!E7),IF(AND(CALCULATION!E5="Yes",CALCULATION!E6="Yes"),COUNTIFS(DATA!D:D,CALCULATION!B10,DATA!H:H,CALCULATION!E4),""))ne hurdle. New file attached

[Glenn Kennedy]

What's the difference between these:
=IF(AND(E5="Yes",E6="Yes"),
COUNTIFS(DATA!D:D,CALCULATION!B10,DATA!J:J,CALCULATION!E7),
IF(AND(CALCULATION!E5="Yes",CALCULATION!E6="Yes"),
COUNTIFS(DATA!D:D,CALCULATION!B10,DATA!H:H,CALCULATION!E4),""))

they are both Yes/Yes responses. Do you mean E4 at the end????

[Beginner041699]

I did mean E4 on the end as you caught

[Glenn Kennedy]

So what do you want excel to count in a yes/yes situation??

Edit @ 17:51 LoL... I made a Jeremy Hunt mis-spelling first time round and the auto-censor replaced my letters with ****

[Beginner041699]

I want Excel to count the IFs for the Yes: =IF($F$5="YES",IF($F$6="YES",COUNTIFS(DATA!$D:$D,B$10,DATA!J:J,$F$7),IF($F$5="YES",COUNTIFS(DATA!$D:$D,B$10,DATA!$I:$I,$F$3,DATA!J:J,CALCULATION!$F$7),COUNTIFS(DATA!$B:$B,$F$2,DATA!$D:$D,B$10,DATA!$I:$I,$F$3,DATA!J:J,CALCULATION!$F$7))),IF($F$6="YES",COUNTIFS(DATA!$B:$B,$F$2,DATA!$D:$D,B$10,DATA!J:J,CALCULATION!$F$7),COUNTIFS(DATA!$B:$B,$F$2,DATA!$D:$D,B$10,DATA!$I:$I,$F$3,DATA!J:J,CALCULATION!$F$7)))

[Glenn Kennedy]

That formula does not seem to belong in your sample sheet. E4 has now vanished. Can you repost a sheet and formula that match each other? Is it your intention that if a DD is left blank, it will count "All"?

[Glenn Kennedy]

Hang on... I think I begin to follow this... Do nothing for a moment.

[Glenn Kennedy]

Try this:

=COUNTIFS(OFFSET(DATA!$D:$D,,ROWS($1:1)-1,,),B$10,DATA!$B:$B,IF($E$2="All","<>",$E$2),DATA!$I:$I,IF($E$3="All","<>",$E$3),DATA!$H:$H,IF($E$4="All","<>",$E$4),DATA!$J:$J,IF($E$5="All","<>",$E$5))

Not extensively checked...

[Glenn Kennedy]

IF that is correct, then this formula is slightly better:

=COUNTIFS(INDEX(DATA!$D:$F,,ROWS($1:1)),B$10,DATA!$B:$B,IF($E$2="All","<>",$E$2),DATA!$I:$I,IF($E$3="All","<>",$E$3),DATA!$H:$H,IF($E$4="All","<>",$E$4),DATA!$J:$J,IF($E$5="All","<>",$E$5))

It avoids the volatile OFFSET function.

[Beginner041699]

SOLVED...I simply did not have the IFs nested correctly with the AND function and had to us 'fx' to properly add ranges and criteria in my COUNTIFs. Not sure it was the most simplified way to get it done but I was able to get the desired outcome

[Glenn Kennedy]

Whatever... did you actually try the solution that I offered?

[Beginner041699]

Hi yes I did try and I did not get the desired outcome.