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.
>Solution :
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")