Forum Discussion

Preston_B's avatar
Preston_B
Copper Contributor
Nov 28, 2023
Solved

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

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.

excel
Formulas and Functions
office 365
  • Preston_B's avatar
    Preston_B
    Dec 07, 2023
    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.
  • djclements's avatar
    djclements
    Bronze Contributor
    Dec 01, 2023

    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
    • Preston_B's avatar
      Preston_B
      Copper Contributor
      Dec 07, 2023
      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.

Resources