CODEWITHSUNDEEP
excelexcel-formulaexcel-tables
dudekdev
dudekdev

Reputation: 3

EXCEL CONCATENATED SUMIFS are ignoring filters

I have table with summary like that.

enter image description here

To achieve that summary I used formula like that:

CONCATENATE(SUMIFS(A2:A13;C2:C13;"=JPY");" JPY";CHAR(10);SUMIFS(A2:A13;C2:C13;"=EUR");" EUR";CHAR(10);SUMIFS(A2:A13;C2:C13;"=PLN");" PLN"; )

The problem is its not working with filters in column. How can I modify this formula to consider filters?

It needs to be in single cell because this sheet is created in java.

Upvotes: 0

Views: 60

Answers (1)

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19737

This explains it fairly well.

The formula would be:

=CONCATENATE(SUMPRODUCT(SUBTOTAL(109,OFFSET(A2,ROW(A2:A13)-ROW(A2),,1)),--(C2:C13="JPY")), " JPY", CHAR(10),
             SUMPRODUCT(SUBTOTAL(109,OFFSET(A2,ROW(A2:A13)-ROW(A2),,1)),--(C2:C13="EUR")), " EUR", CHAR(10),
             SUMPRODUCT(SUBTOTAL(109,OFFSET(A2,ROW(A2:A13)-ROW(A2),,1)),--(C2:C13="PLN")), " PLN")

or for ease of copying in your locality:

=CONCATENATE(SUMPRODUCT(SUBTOTAL(109;OFFSET(A2;ROW(A2:A13)-ROW(A2);;1));--(C2:C13="JPY")); " JPY"; CHAR(10);
             SUMPRODUCT(SUBTOTAL(109;OFFSET(A2;ROW(A2:A13)-ROW(A2);;1));--(C2:C13="EUR")); " EUR"; CHAR(10);
             SUMPRODUCT(SUBTOTAL(109;OFFSET(A2;ROW(A2:A13)-ROW(A2);;1));--(C2:C13="PLN")); " PLN")

Upvotes: 1

Related Questions