VBA help - sum formula as per criteria list

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
976
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
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,243
Messages
6,170,964
Members
452,371
Latest member
Frana

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
Back
Top