Forum Discussion

Lycias's avatar
Lycias
Brass Contributor
Sep 18, 2021
Solved

summing with multiple criteria

I would like to find the total popo (second column below) for each category name in the first column for each class (third column) only for rows that are not empty in the last column (criteria).   ...
Formulas and Functions
  • bosinander's avatar
    bosinander
    Sep 18, 2021

    Lycias If using Excel 365 you can use dynamic arrays to FILTER the three lines and then SUMIFS to get the totals. Two formulas and a third to pull the data to another table.

     

     

    Formula 1, G3

    1. defines the data range as a3:d11.
    2. The wanted data is all rows in dataRange and column 1 and 3.
    3. Criteria is all rows (zero means all rows when indexing only one column) column 4
    4. Output is data columns where the length of the criteria column is greater than 0

    =LET(dataRange;A3:D11;
    dataCols;INDEX(dataRange;SEQUENCE(ROWS(dataRange));{1\3});
    criteraCol;INDEX(dataRange;0;4);
    output;FILTER(dataCols;LEN(criteraCol)>0);
    output
    )

    The last parameter "output" may be changed to the other names for exploring each part.

     

    Formula 2, i3

    G3# is the dynamic range anchored in cell G3:

    INDEX(G3#;0;1) means all rows (zero) and column 1 being the filtered names.
    D3:D11;"<>" means not empty.

    =SUMIFS(B3:B11;A3:A11;INDEX(G3#;0;1);C3:C11;INDEX(G3#;0;2);D3:D11;"<>")

     

    Formula 3, p3

    Concatenates m3:m14 with a pipe and n3:n14.
    The combination is then xlookup'ed in the same combination of name and class. 

    =XLOOKUP(M3:M14 & "|" & N3:N14;INDEX(G3#;0;1) & "|" & INDEX(G3#;0;2);I3#;"")

     

    Formula 1 and 2 gives in much result similar to a pivot table but this does not need manual updating.

    The solution uses functions as of now available only in Excel 365 but will be available in Excel 2021 .

Resources