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.

 

[Lebene]

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.

I definitely need to go back a step and read up on those as I don't know. I'm having a "whoosh" moment right now. In any case, I am grateful for your help.