Nestled IF with COUNTIFS question

[Lebene]

Hi all, my first query. I always lurk on this forum for help on things but this time my googling skills have failed me so I just had to suck it up and make an account. A little about me and my motivations: Over the past 10 months I have been upskilling myself. I am a novice in dashboards, data visualisation and analytics but I'm learning from scratch on the job. I hope to start formal learning soon. I really enjoy eureka moments and learning new Excel formulas and tricks.

What I want: I want my IF(countifs to recognise attendance marked 'Yes' or (not and) 'Declared' and return two values based on that Yes = Attended, Declared = Declared, and if it says anything else like 'Declined' or 'DNA', it should return 'No'.

At the moment I have it returning 'Attended' and 'No' for 'Yes' and everything else respectively but I need my dashboard to properly capture the difference between a declared non-attendance and declined/dna.

Example (lots of headers missing but this is the gist of how it returns):
[TABLE="width: 764"]
<tbody>[TR]
[TD="class: xl68"]Launch event[/TD]
[TD="class: xl65, width: 11"][/TD]
[TD="class: xl67, width: 150"]Attended[/TD]
[TD="class: xl67, width: 150"]Attended[/TD]
[TD="class: xl67, width: 150"]No[/TD]
[TD="class: xl67, width: 150"]Attended[/TD]
[/TR]
</tbody>[/TABLE]

Why?: This way the overall attendance percentages for the workshop itself will show as 100% if all expected attendees attend. But the individual rating will differ.

My current formula:

=IF(COUNTIFS('Master attendance'!$A:$A,"GROUP 1",'Master attendance'!$D:$D,Aspire!I$1,'Master attendance'!$C:$C,"Launch event",'Master attendance'!$F:$F,"Yes")>0,"Attended","No")

There are two groups attending same named events on different dates, hence the extra condition.

Thank you in advance for any insight.

 

[jasonb75]

Not sure if this will work as planned, but if I'm following correctly then something like

=IFERROR(LOOKUP(2,1/COUNTIFS('Master attendance'!$A:$A,"GROUP 1",'Master attendance'!$D:$D,Aspire!I$1,'Master attendance'!$C:$C,"Launch event",'Master attendance'!$F:$F,{"Yes","Declared"}),"Attended","Declared"),"No")

 

[Lebene]

Not sure if this will work as planned, but if I'm following correctly then something like

=IFERROR(LOOKUP(2,1/COUNTIFS('Master attendance'!$A:$A,"GROUP 1",'Master attendance'!$D:$D,Aspire!I$1,'Master attendance'!$C:$C,"Launch event",'Master attendance'!$F:$F,{"Yes","Declared"}),"Attended","Declared"),"No")

Hi Jason,

Thank you for your comment, I keep getting the too many arguments argument... it highlights the "Declared" in the last part: "Attended","Declared")...

 

[Lebene]

Not sure if this will work as planned, but if I'm following correctly then something like

=IFERROR(LOOKUP(2,1/COUNTIFS('Master attendance'!$A:$A,"GROUP 1",'Master attendance'!$D:$D,Aspire!I$1,'Master attendance'!$C:$C,"Launch event",'Master attendance'!$F:$F,{"Yes","Declared"}),"Attended","Declared"),"No")

Do you reckon I should use an INDEX/MATCH instead and just change my input in my main database to match? It would need to pull the attendance result but reference by the attendees name and also the event. Is that possible? I've only done indexmatches with one lookup value.

 

[Fluff]

Try it like
=IFERROR(LOOKUP(2,1/COUNTIFS('Master attendance'!$A:$A,"GROUP 1",'Master attendance'!$D:$D,Aspire!I$1,'Master attendance'!$C:$C,"Launch event",'Master attendance'!$F:$F,{"Yes","Declared"}),{"Attended","Declared"}),"No")

 

[Lebene]

Try it like
=IFERROR(LOOKUP(2,1/COUNTIFS('Master attendance'!$A:$A,"GROUP 1",'Master attendance'!$D:$D,Aspire!I$1,'Master attendance'!$C:$C,"Launch event",'Master attendance'!$F:$F,{"Yes","Declared"}),{"Attended","Declared"}),"No")

Hi Fluff,

Thank you so much, this worked!! Ihave to admit the LOOKUP(2,1/COUNTIFS section is completely foreign to me, if you know a good article explaining this so I can learn myself, I'd really appreciate it. Thanks again.

 

[Fluff]

Glad it's working & thanks for the feedback.
I don't fully understand the formula myself (I'm more VBA based), I just modified Jason's formula, so can't really explain how it works.

 

[jasonb75]

The use of 1/something is to force a #DIV/0! error from results that equal zero, which can then be used to ignore zero results when combined with a function that ignores errors.

In the formula used here, the countifs part is counting 2 sets of criteria in one formula, {"attended","declared"}. If any of the counts has a zero result then 1/countifs generates a #DIV/0! error.

Lookup(2,1/countifs( finds the result farthest right in the array that is not an error, or in other words, has a positive count result.

If the results are all "attended" then "declared" will return an error based on a zero count, so lookup will only see "attended".
If the results are all "declared" then "attended" will return an error based on a zero count, so lookup will only see "declared".
If the results are a mix of "attended" and "declared" then neither will return an error so lookup will retrieve the rightmost result of "declared"
If there are no "attended" or "declared" then both counts will return an error, this means there is nothing for lookup to find, this is trapped by IFERROR to return "no".

 

[Lebene]

The use of 1/something is to force a #DIV/0! error from results that equal zero, which can then be used to ignore zero results when combined with a function that ignores errors.

In the formula used here, the countifs part is counting 2 sets of criteria in one formula, {"attended","declared"}. If any of the counts has a zero result then 1/countifs generates a #DIV/0! error.

Lookup(2,1/countifs( finds the result farthest right in the array that is not an error, or in other words, has a positive count result.

If the results are all "attended" then "declared" will return an error based on a zero count, so lookup will only see "attended".
If the results are all "declared" then "attended" will return an error based on a zero count, so lookup will only see "declared".
If the results are a mix of "attended" and "declared" then neither will return an error so lookup will retrieve the rightmost result of "declared"
If there are no "attended" or "declared" then both counts will return an error, this means there is nothing for lookup to find, this is trapped by IFERROR to return "no".

Hi Jason, wow this is so fascinating. I hope you don't mind me parroting back because I really am a novice still.

So basically 1/something is creating an error from "empty" values (any value that doesn't match my criteria classifies as "empty"?) so that IFERROR can now pick it up to return "No". Meanwhile, COUNTIFS is encompassing the "Yes" and "declared" and the repeat of the {"attended","declared"} is telling the COUNTIFS what to return visually if either former value is found?

I won't lie and say I understand your last part but I felt the same way about index match just 2.5 months ago, so hopefully I can grow and have a eureka moment. I think it's

If the results are a mix of "attended" and "declared" then neither will return an error so lookup will retrieve the rightmost result of "declared"

that has confused me.
I understand the other two as: Since Excel cannot find "Declared" in that cell, it looks for "Yes", if it finds "Yes" it will return "Attended" and vice versa. If it cannot find either, it will be forced into an error by 1/something and IFERROR will pick it up to return "No".

Thanks again so much for this, it will probably take me a couple months to confidently write the formula myself but I hope I've understood what you explained well, as it's just one step closer...

 

[jasonb75]

Lets say that you have "yes" 4 times and "declared" twice, so LOOKUP(2,1/COUNTIFS({"yes","declared"}) will equate to LOOKUP(2,1/({4,2}, or LOOKUP(2,{0.25,0.5}

LOOKUP finds the last (non error) value in the array that is less than or equal to the lookup value of 2. (See excel help for more information on how lookup works). If you understand how MATCH works with approximate match rather than exact match then the priniplce here is similar, except that the order is not sorted.