VBA help - sum formula as per criteria list

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
853
Office Version
  1. 2010
Platform
  1. Windows
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
1Expense headCost Unit1Cost Unit2Grand TotalAddition CriteriaSubsctraction Criteria
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
10PF Admin EDLI Charges227.84717.69800
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
 

Some videos you may like

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
Joined
Feb 4, 2009
Messages
853
Office Version
  1. 2010
Platform
  1. Windows
Hi Team,

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

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

Thanks
mg
 

rondeondo

Board Regular
Joined
Aug 15, 2012
Messages
156
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
Joined
Feb 4, 2009
Messages
853
Office Version
  1. 2010
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
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.
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
Top