RAJESH1960
Banned for repeated rules violations
- Joined
- Mar 26, 2020
- Messages
- 2,313
- Office Version
- 2019
- Platform
- Windows
Hello guys
I need your expert help to add a condition to the formula. I want to edit the formula in column J to get the correct result. If the rates in column N are different then the formula in column J should add the taxable value in column H and display in column J. If the rates in column N are same then the formula in column J should not add the taxable value but show one value only in column J.
I need your expert help to add a condition to the formula. I want to edit the formula in column J to get the correct result. If the rates in column N are different then the formula in column J should add the taxable value in column H and display in column J. If the rates in column N are same then the formula in column J should not add the taxable value but show one value only in column J.
Book1 | |||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | |||
1 | A | B | TIN | NAME | Invoice number | C | D | Taxable Value (₹) | IGST | combine taxable | Combined Tax | CGST | SGST | Rate (%) | |||
2 | 1524 | 39AKXPR3359E3ZJ | Customer 1 | IN-ZSHB-37105 | 2159.32 | 0.00 | 2159.32 | 194.34 | 194.34 | 194.34 | 18 | ||||||
3 | 11 | 39AALCA3377L3Z6 | Customer 2 | SMN0083052002577 | 11863.56 | 0.00 | 23727.12 | 2135.44 | 1067.72 | 1067.72 | 18 | MISTAKE | |||||
4 | 338 | 39AALCA3377L3Z6 | Customer 2 | SMN0083052002577 | 11863.56 | 0.00 | Delete Row | Delete Row | 1067.72 | 1067.72 | 18 | ||||||
5 | 1645 | 34AAECB3833N3ZU | Customer 3 | GST-530 | 26733.50 | 1336.68 | 26733.50 | 1336.68 | 0.00 | 0.00 | 5 | ||||||
6 | 86 | 39AACCC8357H3ZE | Customer 4 | 181/2020-21 | 1300.00 | 0.00 | 1300.00 | 117.00 | 117.00 | 117.00 | 18 | ||||||
7 | 1615 | 39ABHPC3938R3Z8 | Customer 5 | ICHABE2021000504 | 12480.50 | 0.00 | 18540.34 | 1968.33 | 1123.25 | 1123.25 | 18 | CORRECT | |||||
8 | 1616 | 39ABHPC3938R3Z8 | Customer 5 | ICHABE2021000504 | 6031.27 | 0.00 | Delete Row | Delete Row | 844.37 | 844.37 | 28 | ||||||
9 | 1617 | 39ABHPC3938R3Z8 | Customer 5 | ICHABE2021000504 | 28.57 | 0.00 | Delete Row | Delete Row | 0.71 | 0.71 | 5 | ||||||
10 | 377 | 37AELPN4353H3ZC | Customer 6 | IN-46253 | 1072.88 | 193.12 | 2145.76 | 386.24 | 0.00 | 0.00 | 18 | MISTAKE | |||||
11 | 68 | 37AELPN4353H3ZC | Customer 6 | IN-46253 | 1072.88 | 193.12 | Delete Row | Delete Row | 0.00 | 0.00 | 18 | ||||||
12 | 618 | 39AAJFD5433B3ZR | Customer 7 | KA-1176-20-21 | 51509.52 | 0.00 | 51509.52 | 1287.74 | 1287.74 | 1287.74 | 5 | ||||||
13 | 783 | 39AAJFD5433B3ZR | Customer 7 | KA-1274-20-21 | 2761.90 | 0.00 | 25714.58 | 1446.21 | 69.05 | 69.05 | 5 | CORRECT | |||||
14 | 784 | 39AAJFD5433B3ZR | Customer 7 | KA-1274-20-21 | 22952.68 | 0.00 | Delete Row | Delete Row | 1377.16 | 1377.16 | 12 | ||||||
15 | 1782 | 37AAHPG6373H3ZO | Customer 8 | 2020-21-1243 | 26880.00 | 1344.00 | 26880.00 | 1344.00 | 0.00 | 0.00 | 5 | ||||||
COMBINE TAXABLE VALUE |
Cell Formulas | ||
---|---|---|
Range | Formula | |
J2:J15 | J2 | =IF(COUNTIFS(C$2:C2,C2,E$2:E2,E2)=1,SUMIFS(H:H,C:C,C2,E:E,E2),"Delete Row") |
K2:K15 | K2 | =IF(COUNTIFS(C$2:C2,C2,E$2:E2,E2)=1,IF(I2<>0,SUMIFS(I:I,C:C,C2,E:E,E2),SUMIFS(L:L,C:C,C2,E:E,E2)),"Delete Row") |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
E2:E15 | Expression | =COUNTIFS(D$2:D$15,D2,E$2:E$15,E2)>1 | text | NO |