# EXCEL CONCATENATED SUMIFS are ignoring filters

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