Forum Discussion

Preston_B's avatar
Preston_B
Copper Contributor
Nov 28, 2023

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(Co...
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