Subtotal + If

Ozzythekid

New Member
Hello,

I have a list where I need to get totals on cur. basis on top of the data as per the filtered account ( A1 ) basis.

I need to type a formula in between E1 and E4 in curr. basis but , I do not know how I can run subtotal with if condition.

Right side of the spreadsheet ( between column H:K , you can see the totals which need to be appeared on top on acc basis after the correct formula )

jpeg of sample : https://imgur.com/1PV344F

excel of sample : https://bit.ly/2GltsgF

Thank you for the help in advance
Ozan

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Maybe try

=SUBTOTAL(109,sum range)

=SUMIFS(\$E\$7:\$E\$18,\$A\$7:\$A\$18,"TRLMWITUIZM",\$F\$7:\$F\$18,"TRY")

for example gives you the total for TRLMWITUIZM in TRY.

But this cannot allow me to get the total on currency basis , when I run this formula it sums all the filtered line without caring the currency , I need one additional formula inside subtotal to identfy the respective currency. Thank You

Hello Aladin, thank you so much, this formula works but with this one I need to input Acc name manually inside the formula, what I am looking for is when I make filtering from Acc column , it should provide me the seperate currency totals for the filtered "Acc"

Aladin Merhaba, ingilizcemden dolayı belki tam ifade edemedim asıl istediğimi , Acc bölümü müşteriyi teslim ediyor ve liste bir müşteriye kesilen faturalardan oluşuyor, bir müşteriye birden fazla kurda faturalama yapılıyor ; benim tepede yazmak istediğim formül bir müşteri filtrelendiği zaman altta birden farklı kurda kesilmiş olan çoklu faturaların kur bazında toplamını alabilmek, yardımın için teşekkürler

=SUMIFS(\$E\$7:\$E\$18,\$A\$7:\$A\$18,"TRLMWITUIZM",\$F\$7:\$F\$18,"TRY")

for example gives you the total for TRLMWITUIZM in TRY.

Hello Aladin, thank you so much, this formula works but with this one I need to input Acc name manually inside the formula, what I am looking for is when I make filtering from Acc column , it should provide me the seperate currency totals for the filtered "Acc"

Aladin Merhaba, ingilizcemden dolayı belki tam ifade edemedim asıl istediğimi , Acc bölümü müşteriyi teslim ediyor ve liste bir müşteriye kesilen faturalardan oluşuyor, bir müşteriye birden fazla kurda faturalama yapılıyor ; benim tepede yazmak istediğim formül bir müşteri filtrelendiği zaman altta birden farklı kurda kesilmiş olan çoklu faturaların kur bazında toplamını alabilmek, yardımın için teşekkürler

It seems your Excel file is not available...

Book1
ABCDEFG
1TRY3859.6
2USD3718
3EUR4233.38
4GBP0
5
6AccInv.Inv. DateDue DateAmountCur.
9TRLMWITUIZMA00586204-02-1807-01-182,327.38EUR
10TRLMWITUIZMGTR201800000140304-16-1807-15-181,906.00EUR
11TRLMWITUIZMGTR201800000145204-17-1807-16-18815.20TRY
12TRLMWITUIZMGTR201800000146304-18-1807-17-18843.70TRY
13TRLMWITUIZMGTR201800000146404-18-1807-17-18843.70TRY
14TRLMWITUIZMGTR201800000146504-18-1807-17-181,357.00TRY
15TRLMWITUIZMA08117512-13-1703-13-18594.00USD
16TRLMWITUIZMA10807005-25-1708-23-17472.00USD
17TRLMWITUIZMA04919507-31-1710-29-172,652.00USD
Sheet1

In G1 enter and copy down:

=SUMPRODUCT(SUBTOTAL(9,OFFSET(\$E\$7,ROW(\$E\$7:\$E\$18)-ROW(\$E\$7),0)),--(\$F\$7:\$F\$18=\$F1))

Is this what you have in mind?

ABCDEFG
1TRY3859.6
2USD3718
3EUR4233.38
4GBP0
5
6AccInv.Inv. DateDue DateAmountCur.
9TRLMWITUIZMA00586204-02-1807-01-182,327.38EUR
10TRLMWITUIZMGTR201800000140304-16-1807-15-181,906.00EUR
11TRLMWITUIZMGTR201800000145204-17-1807-16-18815.20TRY
12TRLMWITUIZMGTR201800000146304-18-1807-17-18843.70TRY
13TRLMWITUIZMGTR201800000146404-18-1807-17-18843.70TRY
14TRLMWITUIZMGTR201800000146504-18-1807-17-181,357.00TRY
15TRLMWITUIZMA08117512-13-1703-13-18594.00USD
16TRLMWITUIZMA10807005-25-1708-23-17472.00USD
17TRLMWITUIZMA04919507-31-1710-29-172,652.00USD

</tbody>
Sheet1

In G1 enter and copy down:

=SUMPRODUCT(SUBTOTAL(9,OFFSET(\$E\$7,ROW(\$E\$7:\$E\$18)-ROW(\$E\$7),0)),--(\$F\$7:\$F\$18=\$F1))

Is this what you have in mind?

Exactly what I need !! Thank you so much Master !!!

Replies
6
Views
351
Replies
1
Views
165
Replies
6
Views
401
Replies
0
Views
630
Replies
6
Views
588

1,203,243
Messages
6,054,361
Members
444,718
Latest member
r0nster

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.

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