Excel: Nest IF condition inside COUNTIF?-CodePudding

I have a few dropdowns to determine what should be used as conditions for data calculations, these are:

Country OR Supervisor (dropdown changes depending on two radio buttons, and content is cleared when selection is made)
Month
Channel

So instead of writing a long and complicated formula like this (where I would replace the text string with the actual COUNTIFS formula corresponding to the text string):

=IFS(
         AND(radioCheck=1,ISBLANK(D3),NOT(ISBLANK(D5)),NOT(ISBLANK(D6))),"Country blank",
         AND(radioCheck=1,ISBLANK(D3),ISBLANK(D5),NOT(ISBLANK(D6))),"Country Month blank",
         AND(radioCheck=1,ISBLANK(D3),NOT(ISBLANK(D5)),ISBLANK(D6)),"Country Channel blank",

         AND(radioCheck=1,NOT(ISBLANK(D3)),ISBLANK(D5),NOT(ISBLANK(D6))),"Month blank",
         AND(radioCheck=1,ISBLANK(D3),ISBLANK(D5),NOT(ISBLANK(D6))),"Country Month blank",
         AND(radioCheck=1,NOT(ISBLANK(D3)),ISBLANK(D5),ISBLANK(D6)),"Month Channel blank",

         AND(radioCheck=1,NOT(ISBLANK(D3)),NOT(ISBLANK(D5)),ISBLANK(D6)),"Channel blank",
         AND(radioCheck=1,ISBLANK(D3),NOT(ISBLANK(D5)),ISBLANK(D6)),"Country Channel blank",
         AND(radioCheck=1,NOT(ISBLANK(D3)),ISBLANK(D5),ISBLANK(D6)),"Month Channel blank",

         AND(radioCheck=1,ISBLANK(D3),ISBLANK(D5),ISBLANK(D6)),"Country Month Channel blank",
         AND(radioCheck=1,NOT(ISBLANK(D3)),NOT(ISBLANK(D5)),NOT(ISBLANK(D6))),"Country Month Channel Chosen",

         AND(radioCheck=2,ISBLANK(D3),NOT(ISBLANK(D5)),NOT(ISBLANK(D6))),"Supervisor blank",
         AND(radioCheck=2,ISBLANK(D3),ISBLANK(D5),NOT(ISBLANK(D6))),"Supervisor Month blank",
         AND(radioCheck=2,ISBLANK(D3),NOT(ISBLANK(D5)),ISBLANK(D6)),"Supervisor Channel blank",

         AND(radioCheck=2,NOT(ISBLANK(D3)),ISBLANK(D5),NOT(ISBLANK(D6))),"Month blank",
         AND(radioCheck=2,ISBLANK(D3),ISBLANK(D5),NOT(ISBLANK(D6))),"Supervisor Month blank",
         AND(radioCheck=2,NOT(ISBLANK(D3)),ISBLANK(D5),ISBLANK(D6)),"Month Channel blank",

         AND(radioCheck=2,NOT(ISBLANK(D3)),NOT(ISBLANK(D5)),ISBLANK(D6)),"Channel blank",
         AND(radioCheck=2,ISBLANK(D3),NOT(ISBLANK(D5)),ISBLANK(D6)),"Supervisor Channel blank",
         AND(radioCheck=2,NOT(ISBLANK(D3)),ISBLANK(D5),ISBLANK(D6)),"Month Channel blank",

         AND(radioCheck=2,ISBLANK(D3),ISBLANK(D5),ISBLANK(D6)),"Supervisor Month Channel blank",
         AND(radioCheck=2,NOT(ISBLANK(D3)),NOT(ISBLANK(D5)),NOT(ISBLANK(D6))),"Supervisor Month Channel Chosen"
)

(radiocheck = 1 means country dropdown, and radiocheck 2 means supervisor dropdown. D3 = country OR supervisor dropdown, D5 is month dropdown and D6 is channel dropdown)

..for every field of my data that I want to be calculated based on these three/four conditions, I want to be able to achieve something like this instead (in the example, month becomes blank, so - remove month as a condition):

I.e:

=COUNTIFS(rawNPS,">=9",
          IF($D$5="","",rawYYMMM,$D$5),
          rawAgent,K20,rawChannel,$D$6
)

..so that IF there is no choice made in the dropdown in D5, then rawYYMMM will NOT be a condition in the formula.

Is this somehow possible? I cannot provide much more examples than this,

CodePudding user response：

If the entries in rawYYMMM and D5 are positive, non-zero numerics:

=COUNTIFS(rawNPS,">=9",rawYYMMM,IF($D$5="",">0",$D$5),rawAgent,K20,rawChannel,$D$6)

If the entries in rawYYMMM and D5 are numerics which may be positive, negative or zero:

=SUM(COUNTIFS(rawNPS,">=9",rawYYMMM,IF($D$5="",{">0","<=0"},$D$5),rawAgent,K20,rawChannel,$D$6))

If the entries in rawYYMMM and D5 are text:

=COUNTIFS(rawNPS,">=9",rawYYMMM,IF($D$5="","*",$D$5),rawAgent,K20,rawChannel,$D$6)