Nov 28 2023 07:50 AM - edited Nov 28 2023 08:00 AM
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.
Dec 01 2023 07:28 AM
@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:
Alternative method:
Dec 07 2023 08:48 AM
SolutionDec 07 2023 08:48 AM
Solution