Subtotal filtered data + SUMIFS

[danielvdh]

Hi,

So far I've been able to find an answer to any question I had on this forum, but as of today I needed to register in order to continue working on my Excel file.

I consider myself as being quite a rookie in Excel so please don't be too harsh on this newbie

I have the following problem: I am creating a turnover report with customers on the rows, in column D the currency of this specific customer (either USD or EUR), and in column E the turnover (drawn from a pivot table).

With the subtotal offset function I managed to create a totals row which accumulates the filtered results, but I want to add a function (like a SUMIFS) to multiply USD numbers ONLY by a fixed conversion rate.
To illustrate what I currently have, please see the screenshot below.

I hope the example and my intentions are clear.
Thanks a lot in advance!

Daniël

 

[puru.sve]

Hi , You may try this


=SUM(IF($C$5:$C$13=C14,IF($D$5:$D$13="USD",($E$5:$E$13)*1.36,$E$5:$E$13)))

Use cntrl + shift+ enter to paste this formula


And here 1.36 are conversion rate , you may make this dynamic by replace this with any cell reference.

 

[danielvdh]

Hi , You may try this


=SUM(IF($C$5:$C$13=C14,IF($D$5:$D$13="USD",($E$5:$E$13)*1.36,$E$5:$E$13)))

Use cntrl + shift+ enter to paste this formula


And here 1.36 are conversion rate , you may make this dynamic by replace this with any cell reference.

Hi,
This works, however it no longer adjusts the total row when I apply a filter which it does in my current formula. So not exactly what I need. Thanks though

 

[puru.sve]

Could you elaborate bit more. I am not sure what you are taking about ?

 

[danielvdh]

Could you elaborate bit more. I am not sure what you are taking about ?

Hi,

Please see the formula bar in the image in my start post. To this formula I want to add a SUMIF or similar to multiply USD amounts by a conversion rate. I hope this is more clear.


Thanks

 

[puru.sve]

I am not sure .. but with sumproduct formula ... you have to use it two times as below


=SUMPRODUCT(--($C$5:$C$13="LAST YEAR TURNOVER"),SUBTOTAL(9,OFFSET($E$5:$E$13,ROW($E$5:$E$13)-MIN(ROW($E$5:$E$13)),0,1)),(D5:D13="USD")*1.75)+SUMPRODUCT(--($C$5:$C$13="LAST YEAR TURNOVER"),SUBTOTAL(9,OFFSET($E$5:$E$13,ROW($E$5:$E$13)-MIN(ROW($E$5:$E$13)),0,1)),(D5:D13="USD")*1)

 

[danielvdh]

I am not sure .. but with sumproduct formula ... you have to use it two times as below


=SUMPRODUCT(--($C$5:$C$13="LAST YEAR TURNOVER"),SUBTOTAL(9,OFFSET($E$5:$E$13,ROW($E$5:$E$13)-MIN(ROW($E$5:$E$13)),0,1)),(D5:D13="USD")*1.75)+SUMPRODUCT(--($C$5:$C$13="LAST YEAR TURNOVER"),SUBTOTAL(9,OFFSET($E$5:$E$13,ROW($E$5:$E$13)-MIN(ROW($E$5:$E$13)),0,1)),(D5:D13="USD")*1)

You made my day! thats exactly what I was looking for. Thanks a lot !