Help with Excel formula summing numbers based on separate criteria(unique & list)

[Preston_B]

Hello Excel experts!  I have a bit of a complex formula that I need help with.  I'm looking to sum the 'check-ins' numbers (column Q with range of 22:20022 to handle any volume of users) if the ID(Column M) is unique and the 'Type' matches a list somewhere else (X7:X16)

Here's a screenshot of the data (don't worry, fake names & ID's)

So far, I have   =SUMIF(P$22:P$20022,X7,Q$22:Q$20022)  but that only returns the sum of one value in the 'Type' range (X7) versus the entire range (X7:X16) and it does not ignore duplicates (ID 555 / column M).  I know I can =sum( 10 variants of the above formula to cover the entire range, but I'd like to learn a better way.

I'd rather not make a helper column to return 1's if the ID(Column M) is unique if possible.

[#2]

@Preston_B Since you've tagged Office 365, you can use the FILTER function to filter column Q, based on the results of two COUNTIF methods used in the include argument, then SUM the results. For example:

=SUM(FILTER(Q22:Q20022, (COUNTIF(M22:M20022, M22:M20022)=1)*COUNTIF(X7:X16, P22:P20022), 0))

Where:

• (COUNTIF(M22:M20022, M22:M20022)=1) determines if each ID in column M is unique
• COUNTIF(X7:X16, P22:P20022) determines if each Type in column P is found in range X7:X16

Alternative method:

• ISNUMBER(XMATCH(P22:P20022, X7:X16)) can also be used instead of COUNTIF(X7:X16, P22:P20022) to filter by the list of Types, if preferred

[#3 · Accepted solution]

Hello! I apologize for the late response. I have tried this solution and it does not calculate properly. The sum should be 124 and it returns 44.
I have, however, found a formula that does work:
SUM(IFNA(IF(P22:P20022=X7,INDEX(UNIQUE(M22:R20022),,5)),0),IFNA(IF(P22:P20022=X8,INDEX(UNIQUE(M22:R20022),,5)),0), etc for all of the x values.