Need to add one condition to the formula

[RAJESH1960]

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.

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

 

[jasonb75]

There is one mismatch only to correct.

If that is the case I would suspect a problem with the data rather than the formula, possibly a typo / trailing space in Tin / Invoice number.

I did notice that the mini sheet in post 15 is shifted over 1 column when compared to the original but if you had set the formula to the wrong layout then I would have expected more errors in the results.

 

[RAJESH1960]

If that is the case I would suspect a problem with the data rather than the formula, possibly a typo / trailing space in Tin / Invoice number.

I did notice that the mini sheet in post 15 is shifted over 1 column when compared to the original but if you had set the formula to the wrong layout then I would have expected more errors in the results.

Yes I found the reference mistake. It worked in the sample sheet. Trying it now in the data base.

 

[RAJESH1960]

Yes I found the reference mistake. It worked in the sample sheet. Trying it now in the data base.

Eureka. Finally got both taxable and tax right. Thank you very much for being with me till the last. If not for you, I would have not continued today. Once again Thanks.?