# Subtotal filtered data + SUMIFS

#### danielvdh

##### New Member
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.

Daniël

### Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
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.

Last edited:
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

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

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

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)

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 !

Replies
13
Views
278
Replies
3
Views
655
Replies
3
Views
2K
Replies
1
Views
160
Replies
7
Views
2K

1,196,280
Messages
6,014,438
Members
441,819
Latest member
Blackov

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back