Subtotal + If

[Ozzythekid]

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

 

[gaz_chops]

Maybe try

=SUBTOTAL(109,sum range)

 

[Aladin Akyurek]

=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.

 

[Ozzythekid]

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

 

[Ozzythekid]

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

 

[Ozzythekid]

=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

 

[Aladin Akyurek]

It seems your Excel file is not available...

 

[Ozzythekid]

It seems your Excel file is not available...

Here is the updated link https://bit.ly/2BkWdXr

 

[Aladin Akyurek]

Book1
	A	B	C	D	E	F	G
1						TRY	3859.6
2						USD	3718
3						EUR	4233.38
4						GBP	0
5
6	Acc	Inv.	Inv. Date	Due Date	Amount	Cur.
9	TRLMWITUIZM	A005862	04-02-18	07-01-18	2,327.38	EUR
10	TRLMWITUIZM	GTR2018000001403	04-16-18	07-15-18	1,906.00	EUR
11	TRLMWITUIZM	GTR2018000001452	04-17-18	07-16-18	815.20	TRY
12	TRLMWITUIZM	GTR2018000001463	04-18-18	07-17-18	843.70	TRY
13	TRLMWITUIZM	GTR2018000001464	04-18-18	07-17-18	843.70	TRY
14	TRLMWITUIZM	GTR2018000001465	04-18-18	07-17-18	1,357.00	TRY
15	TRLMWITUIZM	A081175	12-13-17	03-13-18	594.00	USD
16	TRLMWITUIZM	A108070	05-25-17	08-23-17	472.00	USD
17	TRLMWITUIZM	A049195	07-31-17	10-29-17	2,652.00	USD
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?

 

[Ozzythekid]

	A	B	C	D	E	F	G
USD
GBP
Acc	Inv.	Inv. Date	Due Date	Cur.
TRLMWITUIZM	A005862	EUR
TRLMWITUIZM	GTR2018000001403	EUR
TRLMWITUIZM	GTR2018000001452	TRY
TRLMWITUIZM	GTR2018000001463	TRY
TRLMWITUIZM	GTR2018000001464	TRY
TRLMWITUIZM	GTR2018000001465	TRY
TRLMWITUIZM	A081175	USD
TRLMWITUIZM	A108070	USD
TRLMWITUIZM	A049195	USD

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DEEBF7]#DEEBF7[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DEEBF7]#DEEBF7[/URL] "]TRY[/TD]
[TD="align: right"]3859.6[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]3718[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DEEBF7]#DEEBF7[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DEEBF7]#DEEBF7[/URL] "]EUR[/TD]
[TD="align: right"]4233.38[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]Amount[/TD]

[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]04-02-18[/TD]
[TD="align: right"]07-01-18[/TD]
[TD="align: right"]2,327.38[/TD]

[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]04-16-18[/TD]
[TD="align: right"]07-15-18[/TD]
[TD="align: right"]1,906.00[/TD]

[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]04-17-18[/TD]
[TD="align: right"]07-16-18[/TD]
[TD="align: right"]815.20[/TD]

[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]04-18-18[/TD]
[TD="align: right"]07-17-18[/TD]
[TD="align: right"]843.70[/TD]

[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]04-18-18[/TD]
[TD="align: right"]07-17-18[/TD]
[TD="align: right"]843.70[/TD]

[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]04-18-18[/TD]
[TD="align: right"]07-17-18[/TD]
[TD="align: right"]1,357.00[/TD]

[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]12-13-17[/TD]
[TD="align: right"]03-13-18[/TD]
[TD="align: right"]594.00[/TD]

[TD="align: right"][/TD]

[TD="align: center"]16[/TD]

[TD="align: right"]05-25-17[/TD]
[TD="align: right"]08-23-17[/TD]
[TD="align: right"]472.00[/TD]

[TD="align: right"][/TD]

[TD="align: center"]17[/TD]

[TD="align: right"]07-31-17[/TD]
[TD="align: right"]10-29-17[/TD]
[TD="align: right"]2,652.00[/TD]

[TD="align: right"][/TD]

</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 !!!