SUBTOTAL IF x cell contain x text

[harky]

i only know how to use sumif
=SUMIF(G6:G81,"Bank",M6:M81)

how to use this for subtotal? if i filter Col A based on project...

 

[Sufiyan97]

Why you don't use SUMIFS

Sum if cells are equal to

To sum numbers when cells are equal to a specific value, you can use the SUMIF or SUMIFS functions. In the example shown, the formula in cell I5 is: =SUMIFS(F5:F16,C5:C16,"red") When this formula is entered, the result is $192. This is the sum of numbers in the range F5:F16 when cells in C5:C15...

exceljet.net

 

[harky]

Why you don't use SUMIFS

Sum if cells are equal to

To sum numbers when cells are equal to a specific value, you can use the SUMIF or SUMIFS functions. In the example shown, the formula in cell I5 is: =SUMIFS(F5:F16,C5:C16,"red") When this formula is entered, the result is $192. This is the sum of numbers in the range F5:F16 when cells in C5:C15...

exceljet.net

i need to filter, if sumif... it dont work
had a list of diff category. i want to filter it and the total is cal based on what filter

 

[Fluff]

If you're happy to use a helper column, you can put this formula in a blank column by the side of your data

=SUBTOTAL(103,A6)

and then you can change your sumif to

=SUMIFS(M6:M81,G6:G81,"Bank",X6:X81,1)

change the X6:X81 to whatever column has the subtotal formula

 

[harky]

If you're happy to use a helper column, you can put this formula in a blank column by the side of your data

=SUBTOTAL(103,A6)

and then you can change your sumif to

=SUMIFS(M6:M81,G6:G81,"Bank",X6:X81,1)

change the X6:X81 to whatever column has the subtotal formula

sorry.. i dont understand..
G6:G81is my column which contain Bank and Cash
M6:M81 is the column i want to sum

i dunno how to use subtotal but i notice subtotal can sumup based on the one i filter which is Column A

 

[Fluff]

What is the last used column in your data?

 

[harky]

What is the last used column in your data?

this is my table

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
2	Project Name	PR #	Issued Date	Item Description	Remarks	Transcation No.	Ref	Credit			Debit			Subtotal
3								$5	$10	Amount In	$5	$10	Amount Out
4	Budget		1 Jan 2021	Balance Bank Account			Bank			$7,263.97				$ 7,263.97
5	Budget		1 Jan 2021	Balance Cash			Cash			$1,549.30				$ 1,549.30
6
7
8
9
10
11
12												Total Expense for this project
13												Categories		Amount
14												Bank		$ 7,263.97
15												Petty Cash		$ 1,549.30
Sheet1

Cell Formulas
Range	Formula
N4:N5	N4	=IF(G4="Bank",J4+M4,IF(G4="Cash",J4+M4))
N14	N14	=SUMIF(G4:G78,"Bank",N4:N78)
N15	N15	=SUMIF(G4:G78,"Cash",N4:N78)

Cells with Data Validation
Cell	Allow	Criteria
G4:G5	List	=$G$83:$G$88

 

[Fluff]

That data does not match your original formula, or your description.

 

[harky]

That data does not match your original formula, or your description.

is correct because the 5 & 10 is for voucher

i had cash, bank & voucher

i think the subtotal column can delete..
mainly is the table at below

 

[harky]

That data does not match your original formula, or your description.

like this

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Project Name	PR #	Issued Date	Item Description	Remarks	Transcation No.	Ref	Credit			Debit			Subtotal
2								$5	$10	Amount In	$5	$10	Amount Out
3	Budget		1 Jan 2021	Balance b/f from Bank Account			Bank			$ 7,263.97				$7,263.97
4	Budget		1 Jan 2021	Balance b/f from Cash			Cash			$ 1,549.30				$1,549.30
5	Budget		1 Jan 2021	Balance b/f from $5 Voucher	Credit In		V5	11						11
6	Budget		1 Jan 2021	Balance b/f from $10 Voucher	Credit In		V10		1					1
7
8
9
10			Current Balance							Total Expense for this project
11			Ref	Categories			Balance			Ref	Categories			Balance
12			Bank	Bank			$7,263.97			Bank	Bank			$ -
13			Cash	Petty Cash			$1,549.30			Cash	Petty Cash			$ -
14			V5	$5			11			$5	$5			0
15			V10	$10			1			$10	$10			0
Sheet1

Cell Formulas
Range	Formula
N3:N6	N3	=IF(G3="Bank",J3+M3,IF(G3="Cash",J3+M3,IF(G3="V5",H3+K3,IF(G3="V10",I3+L3))))
G12	G12	=SUMIF(G3:G6,"Bank",N3:N6)
G13	G13	=SUMIF(G3:G6,"Cash",N3:N6)
G14	G14	=SUMIF(G3:G6,"V5",N3:N6)
G15	G15	=SUMIF(G3:G6,"V10",N3:N6)
N12	N12	=SUMIF(G3:G6,"Bank",M3:M6)
N13	N13	=SUMIF(G3:G6,"Cash",M3:M6)
N14	N14	=SUMIF(G3:G6,"V5",M3:M6)
N15	N15	=SUMIF(G3:G6,"V10",M3:M6)

Cells with Data Validation
Cell	Allow	Criteria
G3:G4	List	=$G$83:$G$88
G5:G6	List	=$C$12:$C$15