# VBA help - sum formula as per criteria list

#### Mallesh23

##### Well-known Member
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
ABCDEFG
2Basic30561718539272695BasicTDS Payable
3City Compensatory Allowance14668258027299641City Compensatory AllowancePF Payable (Employer)
4H.R.A.12225287416110H.R.A.PF Payable (Employee)
5Children Edu Allowance011085847Children Edu AllowanceCafetaria Deduction
6Special Allowance085847630Special AllowanceTransportation Deduction
7Shift Allowance06300Shift Allowance
8
9PF- Employer contribution3667862264945.49
11Labour Welfare Fund Employer507500
12
13TDS Payable01375505745.49
14PF Payable (Employer)277.85467.6989893
15PF Payable (Employee)3667862260
16Labour Welfare Fund000
17
18Cafetaria Deduction23028402400
19Transportation Deduction15022500
20
21Salary Payable56846.21203211.31565830
Sheet1
Cell Formulas
RangeFormula
D18:D19,D13:D16,D9:D11,D2:D7D2=SUM(B3:C3)
B14:C14B14=+B10+B11
B16:C16B16=B12*1.5
B21:D21B21=SUM(B2+B3+B4+B5+B6+B7+B9+B11)-(B13+B14+B15+B19+B18)

Thanks
mg

### Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

#### Mallesh23

##### Well-known Member
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

##### Board Regular
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

##### Well-known Member
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

Replies
1
Views
58
Replies
20
Views
315
Replies
4
Views
124
Replies
3
Views
117
Replies
4
Views
209

1,128,086
Messages
5,628,589
Members
416,326
Latest member
NinaChristal

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

### Which adblocker are you using?

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