VBA help - sum formula as per criteria list

[Mallesh23]

Hi Team,

Need sum formula help,
Macro to sum ranges as per Addition Criteria and substract from it as per Substraction Criteria.

Expected output in Range("B21:D21") something like this :=> =SUM(B2+B3+B4+B5+B6+B7+B9+B11)-(B13+B14+B15+B19+B18)

Given dummy Data with Criteria.

Book1
	A	B	C	D	E	F	G
1	Expense head	Cost Unit1	Cost Unit2	Grand Total		Addition Criteria	Subsctraction Criteria
2	Basic	30561	718539	272695		Basic	TDS Payable
3	City Compensatory Allowance	14668	258027	299641		City Compensatory Allowance	PF Payable (Employer)
4	H.R.A.	12225	287416	110		H.R.A.	PF Payable (Employee)
5	Children Edu Allowance	0	110	85847		Children Edu Allowance	Cafetaria Deduction
6	Special Allowance	0	85847	630		Special Allowance	Transportation Deduction
7	Shift Allowance	0	630	0		Shift Allowance
8
9	PF- Employer contribution	3667	86226	4945.49
10	PF Admin EDLI Charges	227.8	4717.69	800
11	Labour Welfare Fund Employer	50	750	0
12
13	TDS Payable	0	137550	5745.49
14	PF Payable (Employer)	277.8	5467.69	89893
15	PF Payable (Employee)	3667	86226	0
16	Labour Welfare Fund	0	0	0
17
18	Cafetaria Deduction	230	2840	2400
19	Transportation Deduction	150	2250	0
20
21	Salary Payable	56846.2	1203211.31	565830
Sheet1

Cell Formulas
Range	Formula
D18:D19,D13:D16,D9:D11,D2:D7	D2	=SUM(B3:C3)
B14:C14	B14	=+B10+B11
B16:C16	B16	=B12*1.5
B21:D21	B21	=SUM(B2+B3+B4+B5+B6+B7+B9+B11)-(B13+B14+B15+B19+B18)

Thanks
mg

 

[Mallesh23]

Hi Team,

I am looking for only sum formula for below range. as per criteria, ignore other sums.

B21:D21

B21

=SUM(B2+B3+B4+B5+B6+B7+B9+B11)-(B13+B14+B15+B19+B18)

Thanks
mg

 

[rondeondo]

Hi MG

I see a couple of options,
1 make another column that uses something like =IF(ISNUMBER(MATCH($A2,$F$2:$F$7,0)),B2,IF(ISNUMBER(MATCH($A2,$G$2:$G$6,0)),-B2,0)) then copy this across then total this colomn
Alternately you can make use of sumproduct but it's a little more to set up
=SUMPRODUCT(--(A2:A19=F2)+(A2:A19=F3)+(A2:A19=F4)+(A2:A19=F5)+(A2:A19=F6)+(A2:A19=F7),B2:B19)-SUMPRODUCT(--(A2:A19=G2)+(A2:A19=G3)+(A2:A19=G4)+(A2:A19=G5)+(A2:A19=G6),B2:B19)

Your example shows including b9 and b11 as part of your positives but their descriptions in A9 ad a11 are not in the column F for inclusions.
You could manually include these or add their descriptions to the columns for additions

 

[Mallesh23]

Hi Rondeondo,

Thanks for your help, user want sum formula as it is easy for him to use. Looking help in vba.
I think needs to loop in Column A, find criteria list, store range as string and later needs to put into sum.

=SUM(B2+B3+B4+B5+B6+B7+B9+B11)-(B13+B14+B15+B19+B18)


Thanks
mg