Forum Discussion
Counting with multiple criteria
I am hoping someone will be able to help me out. It's greatly appreciated!
My relevant columns/headers: User Name and Core Certification.
I *think* a part of my issue is that user names are listed multiple times in rows. For instance, if Akila has both an ABC and DEF certification, she will be listed on two rows. If Bob has three different ABC certs, his name is listed in three rows.
For my purposes, I need to count individuals who have 2 or more ABC certs. If a person has only 1 ABC cert, they do not count as a resource in this instance.
I have this in a table and am using a pivot table, which is great, but it still doesn't show me the deficit between how many resources we have with 2+ ABC certifications and how many we need.
Thank you so much!
3 Replies
There's probably a better option depending on your version of Excel & OS (cf. Welcome to your Excel discussion space!). Assuming I understood and you run 2021 or 365
In E3 and down this counts, per Core Certification, how many users have that Certif. excluding users who have less than 2 Certif.:=LET( UserNames, UNIQUE(Table1[User Name]), CertifCount, COUNTIF(Table1[User Name], UserNames), MoreOneCertif, FILTER(UserNames, CertifCount > 1), GoodUsers, ISNUMBER(XMATCH(Table1[User Name], MoreOneCertif)), COUNTA(FILTER(Table1[User Name], (Table1[Core Certification]=D3) * GoodUsers)) )
If not what you expect please provide:
- Version of Excel + OS (Windows / Mac)
- If nothing conditional share your workbook (i.e. OneDrive, Google Drive...) otherwise post at least a picture showing your Table & Pivot
- Post a picture of the expected result- Thank you! I am running 365. I will try this solution on Monday and post more detail if it doesn't work for me. I really appreciate you taking the time to give me an answer!
