CODEWITHSUNDEEP
excelexcel-formula
JBerggreen
JBerggreen

Reputation: 29

Crosstab Excel - match with multiple criteria and return multiple values to one cell

I am trying to return a combined value in a crosstab in excel. Example of the crosstab:

enter image description here

I want to make a formula that puts the number together for the criteria Unknown and Acc1 meaning 70+80 = 150. So I want 1 formula in a single cell to return 150. But the issue is that Unknown and Acc1 might switch spots when I download data, so it has to be dynamic for column A and row 1. I have tried to Xlookup this and manage to find 70 but not a formula that returned 150.

This also means that I can't have a formula that wants to find the sum of column B, as this might be called Acc2 next month.

Hopes this makes sense. Thanks.

Upvotes: 0

Views: 177

Answers (1)

Mayukh Bhattacharya
Mayukh Bhattacharya

Reputation: 27233

Try using SUMPRODUCT( ) or SUM( )

enter image description here

• Formula used in cell B6

=SUMPRODUCT(--(A2:A4="Unknown")*(B1:D1="Acc1")*B2:D4)

You could try the following as well:

enter image description here

• Formula used in cell F3

=LET(
     _lastCol, MATCH(2,1/(1:1<>"")),
     _criteriaOne, B1:INDEX(1:1,,_lastCol)="Acc1",
     _lastRow, MATCH(2,1/(A:A<>"")),
     _criteriaTwo, A2:INDEX(A:A,_lastRow)="Unknown",
     _data, B2:INDEX(B:ZZ,_lastRow,_lastCol),
     _answer, SUM(IFERROR(--(_criteriaOne)*--(_criteriaTwo)*_data,0)),
     _answer)

Or,

enter image description here

• Formula used in cell F2

=LET(
     a, A:E,
     b, FILTER(a,BYROW(a,LAMBDA(r,COUNTA(r)>0))),
     c, TOROW(TAKE(a,1),3),
     d, TOCOL(TAKE(a,,1),3),
     e, DROP(a,1,1),
     f, WRAPROWS(TOCOL(e,1),COLUMNS(c)),
     SUM(--("Unknown"=d)*--("Acc1"=c)*f))

Upvotes: 2

Related Questions