Sep 18 2021 06:39 AM
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).
Any help?
Name | pop | class | criteria |
A | 38107 | Medium | |
A | 27720 | Medium | |
A | 80542 | Medium | |
A | 55828 | Medium | k |
B | 23771 | Medium | |
B | 99218 | Medium | |
C | 30365 | Low | |
C | 35420 | Very High | s-z-y |
C | 8930 | High | y-h-f |
Thank you so much.
Sep 18 2021 06:51 AM
It could be done with PivotTable. Add data to data model creating it and use measure
Total pop:=CALCULATE( SUM(Table1[pop]), NOT( ISBLANK( Table1[criteria] ) ) )
Result is
Sep 18 2021 06:54 AM
With Class added to the pivot table:
Sep 18 2021 07:04 AM
Sep 18 2021 07:52 AM
@Lycias , you just use another data source - if I understood the question correctly.
Sep 18 2021 02:19 PM
Solution@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
=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 .
Sep 19 2021 01:35 AM
@Lycias Using Tables, the solution is more readable and expands the source data. Added another sheet using Tables.
Sep 19 2021 01:37 AM
Sep 19 2021 01:49 AM
Sep 19 2021 02:02 AM
@Lycias A summary by Name only..?
dataCols (output dimensions) is then indexing only the first column (zero to get all rows).
Since there will be duplicates, UNIQUE is added to get them only once each.
=LET(dataRange;A3:D23;
dataCols;INDEX(dataRange;0;1);
criteraCol;INDEX(dataRange;0;4);
names;FILTER(dataCols;LEN(criteraCol)>0);
output;UNIQUE(names);
output
)
Sep 19 2021 04:18 AM
@bosinander, this is what get on a sample of the data......somehow the SUMIFS doesn't seem to recognize that I want the sum for each region, for each class only when it is not empty for the cov column. kindly se attached. not sure where it is not right. For this region, the sum I want should be 148 530 for class "high" but it is giving me a total which i know is for all "high" for region a not only those "high" that are not empty in column "cov". Thanks!
Sep 19 2021 05:15 AM
Sep 18 2021 02:19 PM
Solution@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
=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 .