Subtotal + If

Ozzythekid

New Member
Joined
Feb 4, 2019
Messages
6
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

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
=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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
=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
 
Upvote 0

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?
 
Upvote 0
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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</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 !!!
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,044
Members
448,543
Latest member
MartinLarkin

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top