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.
Thanks
mg
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