EXCEL CONCATENATED SUMIFS are ignoring filters-CodePudding

I have table with summary like that.

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.

CodePudding user response：

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")