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]

Is it ok to add a helper column? I'm not going to say that it will be impossible without, but it's not going to be far from it.

 

[RAJESH1960]

Is it ok to add a helper column? I'm not going to say that it will be impossible without, but it's not going to be far from it.

Column J is the helper column. Since I have not added a condition in the formula, it is combining all the taxable amounts. I think one more if condition in the formula about the rate, will help to solve the problem. But as there is a condition to match the TIN and Name also is a bit of a challenge for me

 

[RAJESH1960]

=IF(N2=N3,J2,IF(COUNTIFS(C$2:C2,C2,E$2:E2,E2)=1,SUMIFS(H:H,C:C,C2,E:E,E2),"Delete Row"))
N2=N3 needs to be edited, then maybe it will work

 

[jasonb75]

Column J is the helper column.

No it isn't, a helper column would be feeding the results to another formula as in the sheet below which yours is not doing.

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	Helper	Combined Tax	CGST	SGST	Rate (%)	combine taxable
2	1524		39AKXPR3359E3ZJ	Customer 1	IN-ZSHB-37105			2159.32	0	2159.32	194.34	194.34	194.34	18	2159.32
3	11		39AALCA3377L3Z6	Customer 2	SMN0083052002577			11863.56	0	11863.56	2135.44	1067.72	1067.72	18	11863.56
4	338		39AALCA3377L3Z6	Customer 2	SMN0083052002577			11863.56	0		Delete Row	1067.72	1067.72	18	Delete Row
5	1645		34AAECB3833N3ZU	Customer 3	GST-530			26733.5	1336.68	26733.5	1336.68	0	0	5	26733.5
6	86		39AACCC8357H3ZE	Customer 4	181/2020-21			1300	0	1300	117	117	117	18	1300
7	1615		39ABHPC3938R3Z8	Customer 5	ICHABE2021000504			12480.5	0	12480.5	1968.33	1123.25	1123.25	18	18540.34
8	1616		39ABHPC3938R3Z8	Customer 5	ICHABE2021000504			6031.27	0	6031.27	Delete Row	844.37	844.37	28	Delete Row
9	1617		39ABHPC3938R3Z8	Customer 5	ICHABE2021000504			28.57	0	28.57	Delete Row	0.71	0.71	5	Delete Row
10	377		37AELPN4353H3ZC	Customer 6	IN-46253			1072.88	193.12	1072.88	386.24	0	0	18	1072.88
11	68		37AELPN4353H3ZC	Customer 6	IN-46253			1072.88	193.12		Delete Row	0	0	18	Delete Row
12	618		39AAJFD5433B3ZR	Customer 7	KA-1176-20-21			51509.52	0	51509.52	1287.74	1287.74	1287.74	5	51509.52
13	783		39AAJFD5433B3ZR	Customer 7	KA-1274-20-21			2761.9	0	2761.9	1446.21	69.05	69.05	5	25714.58
14	784		39AAJFD5433B3ZR	Customer 7	KA-1274-20-21			22952.68	0	22952.68	Delete Row	1377.16	1377.16	12	Delete Row
15	1782		37AAHPG6373H3ZO	Customer 8	2020-21-1243			26880	1344	26880	1344	0	0	5	26880
Sheet1

Cell Formulas
Range	Formula
J2:J15	J2	=IF(COUNTIFS(C$2:C2,C2,E$2:E2,E2,N$2:N2,N2)=1,H2,"")
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")
O2:O15	O2	=IF(COUNTIFS(C$2:C2,C2,E$2:E2,E2)=1,SUMIFS(J:J,C:C,C2,D:D,D2,E:E,E2),"Delete Row")

 

[RAJESH1960]

No it isn't, a helper column would be feeding the results to another formula as in the sheet below which yours is not doing.

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	Helper	Combined Tax	CGST	SGST	Rate (%)	combine taxable
2	1524		39AKXPR3359E3ZJ	Customer 1	IN-ZSHB-37105			2159.32	0	2159.32	194.34	194.34	194.34	18	2159.32
3	11		39AALCA3377L3Z6	Customer 2	SMN0083052002577			11863.56	0	11863.56	2135.44	1067.72	1067.72	18	11863.56
4	338		39AALCA3377L3Z6	Customer 2	SMN0083052002577			11863.56	0		Delete Row	1067.72	1067.72	18	Delete Row
5	1645		34AAECB3833N3ZU	Customer 3	GST-530			26733.5	1336.68	26733.5	1336.68	0	0	5	26733.5
6	86		39AACCC8357H3ZE	Customer 4	181/2020-21			1300	0	1300	117	117	117	18	1300
7	1615		39ABHPC3938R3Z8	Customer 5	ICHABE2021000504			12480.5	0	12480.5	1968.33	1123.25	1123.25	18	18540.34
8	1616		39ABHPC3938R3Z8	Customer 5	ICHABE2021000504			6031.27	0	6031.27	Delete Row	844.37	844.37	28	Delete Row
9	1617		39ABHPC3938R3Z8	Customer 5	ICHABE2021000504			28.57	0	28.57	Delete Row	0.71	0.71	5	Delete Row
10	377		37AELPN4353H3ZC	Customer 6	IN-46253			1072.88	193.12	1072.88	386.24	0	0	18	1072.88
11	68		37AELPN4353H3ZC	Customer 6	IN-46253			1072.88	193.12		Delete Row	0	0	18	Delete Row
12	618		39AAJFD5433B3ZR	Customer 7	KA-1176-20-21			51509.52	0	51509.52	1287.74	1287.74	1287.74	5	51509.52
13	783		39AAJFD5433B3ZR	Customer 7	KA-1274-20-21			2761.9	0	2761.9	1446.21	69.05	69.05	5	25714.58
14	784		39AAJFD5433B3ZR	Customer 7	KA-1274-20-21			22952.68	0	22952.68	Delete Row	1377.16	1377.16	12	Delete Row
15	1782		37AAHPG6373H3ZO	Customer 8	2020-21-1243			26880	1344	26880	1344	0	0	5	26880
Sheet1

Cell Formulas
Range	Formula
J2:J15	J2	=IF(COUNTIFS(C$2:C2,C2,E$2:E2,E2,N$2:N2,N2)=1,H2,"")
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")
O2:O15	O2	=IF(COUNTIFS(C$2:C2,C2,E$2:E2,E2)=1,SUMIFS(J:J,C:C,C2,D:D,D2,E:E,E2),"Delete Row")

Jsonb75. The result in column O is correct. I have to add another helper column along with column J. Right.
Is it possible to get the same result as in O in column J in place of the data in column J.?

 

[RAJESH1960]

This is how the original data is displayed. I have added 2 helper columns, one is combined tax and the other is combined taxable. I have to add another additional helper column for combined tax too as it has also to be changed. total of 4 columns. If possible I want to make it in 2 columns only to make it less complicated.

combine taxable value.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	A	B	TIN	NAME	Invoice number	C	D	Taxable Value (₹)	IGST	CGST	SGST	Rate (%)
2	1524		39AKXPR3359E3ZJ	Customer 1	IN-ZSHB-37105			2159.32	0.00	194.34	194.34	18
3	11		39AALCA3377L3Z6	Customer 2	SMN0083052002577			11863.56	0.00	1067.72	1067.72	18
4	338		39AALCA3377L3Z6	Customer 2	SMN0083052002577			11863.56	0.00	1067.72	1067.72	18
5	1645		34AAECB3833N3ZU	Customer 3	GST-530			26733.50	1336.68	0.00	0.00	5
6	86		39AACCC8357H3ZE	Customer 4	181/2020-21			1300.00	0.00	117.00	117.00	18
7	1615		39ABHPC3938R3Z8	Customer 5	ICHABE2021000504			12480.50	0.00	1123.25	1123.25	18
8	1616		39ABHPC3938R3Z8	Customer 5	ICHABE2021000504			6031.27	0.00	844.37	844.37	28
9	1617		39ABHPC3938R3Z8	Customer 5	ICHABE2021000504			28.57	0.00	0.71	0.71	5
10	377		37AELPN4353H3ZC	Customer 6	IN-46253			1072.88	193.12	0.00	0.00	18
11	68		37AELPN4353H3ZC	Customer 6	IN-46253			1072.88	193.12	0.00	0.00	18
12	618		39AAJFD5433B3ZR	Customer 7	KA-1176-20-21			51509.52	0.00	1287.74	1287.74	5
13	783		39AAJFD5433B3ZR	Customer 7	KA-1274-20-21			2761.90	0.00	69.05	69.05	5
14	784		39AAJFD5433B3ZR	Customer 7	KA-1274-20-21			22952.68	0.00	1377.16	1377.16	12
15	1782		37AAHPG6373H3ZO	Customer 8	2020-21-1243			26880.00	1344.00	0.00	0.00	5
Combine taxable values (2)

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]

Is it possible to get the same result as in O in column J in place of the data in column J.?

I don't think that it will be possible without the helper column. If it can be done then it will require a very complex and inefficient formula, especially given that you don't have office 365.

I have added 2 helper columns, one is combined tax and the other is combined taxable.

As I said earlier, those are not helper columns, they are summary formulas that are not working. A helper creates an intermediate result that another formula can use in order to return the final result more efficiently.

 

[RAJESH1960]

I don't think that it will be possible without the helper column. If it can be done then it will require a very complex and inefficient formula, especially given that you don't have office 365.

As I said earlier, those are not helper columns, they are summary formulas that are not working. A helper creates an intermediate result that another formula can use in order to return the final result more efficiently.

Ok. I know it is a little complicated. Let me try your way of doing it. With the help of your formula, I will edit the formula with your this part of the formula N$2:N2,N2)=1,H2,""). That will be helpful. Will share it with you once I get it.

 

[RAJESH1960]

I understood your formula and did some editing and got the required result. Thanks Jasonb75.

combine taxable value.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	A	B	TIN	NAME	Invoice number	C	D	Taxable Value (₹)	IGST	Helper 1	Combined Taxable	Helper 2	Combine tax	CGST	SGST	Rate (%)
2	1524		39AKXPR3359E3ZJ	Customer 1	IN-ZSHB-37105			2159.32	0.00	2159.32	2353.66	194.34	212.34	194.34	194.34	18
3	11		39AALCA3377L3Z6	Customer 2	SMN0083052002577			11863.56	0.00	11863.56	12931.28	1067.72	1085.72	1067.72	1067.72	18
4	338		39AALCA3377L3Z6	Customer 2	SMN0083052002577			11863.56	0.00	Delete Row	Delete Row	Delete Row	Delete Row	1067.72	1067.72	18
5	1645		34AAECB3833N3ZU	Customer 3	GST-530			26733.50	1336.68	26733.5	26733.5	0	5	0.00	0.00	5
6	86		39AACCC8357H3ZE	Customer 4	181/2020-21			1300.00	0.00	1300	1417	117	135	117.00	117.00	18
7	1615		39ABHPC3938R3Z8	Customer 5	ICHABE2021000504			12480.50	0.00	12480.5	20508.67	1123.25	1986.33	1123.25	1123.25	18
8	1616		39ABHPC3938R3Z8	Customer 5	ICHABE2021000504			6031.27	0.00	6031.27	Delete Row	844.37	Delete Row	844.37	844.37	28
9	1617		39ABHPC3938R3Z8	Customer 5	ICHABE2021000504			28.57	0.00	28.57	Delete Row	0.71	Delete Row	0.71	0.71	5
10	377		37AELPN4353H3ZC	Customer 6	IN-46253			1072.88	193.12	1072.88	1072.88	0	18	0.00	0.00	18
11	68		37AELPN4353H3ZC	Customer 6	IN-46253			1072.88	193.12	Delete Row	Delete Row	Delete Row	Delete Row	0.00	0.00	18
12	618		39AAJFD5433B3ZR	Customer 7	KA-1176-20-21			51509.52	0.00	51509.52	52797.26	1287.74	1292.74	1287.74	1287.74	5
13	783		39AAJFD5433B3ZR	Customer 7	KA-1274-20-21			2761.90	0.00	2761.9	27160.79	69.05	1451.21	69.05	69.05	5
14	784		39AAJFD5433B3ZR	Customer 7	KA-1274-20-21			22952.68	0.00	22952.68	Delete Row	1377.16	Delete Row	1377.16	1377.16	12
15	1782		37AAHPG6373H3ZO	Customer 8	2020-21-1243			26880.00	1344.00	26880	26880	0	5	0.00	0.00	5
16
17			FINAL RESULT
18	A	B	TIN	NAME	Invoice number	C	D	Taxable Value (₹)	IGST	CGST	SGST	Rate (%)
19	377		37AELPN4353H3ZC	Customer 6	IN-46253			1072.88	193.12	0	0	18
20	1645		34AAECB3833N3ZU	Customer 3	GST-530			26733.5	1336.68	0	0	5
21	1782		37AAHPG6373H3ZO	Customer 8	2020-21-1243			26880	1344.00	0	0	5
22	86		39AACCC8357H3ZE	Customer 4	181/2020-21			1300	0.00	117	117	18
23	1524		39AKXPR3359E3ZJ	Customer 1	IN-ZSHB-37105			2159.32	0.00	194.34	194.34	18
24	11		39AALCA3377L3Z6	Customer 2	SMN0083052002577			11863.56	0.00	1067.72	1067.72	18
25	618		39AAJFD5433B3ZR	Customer 7	KA-1176-20-21			51509.52	0.00	1287.74	1287.74	5
26	783		39AAJFD5433B3ZR	Customer 7	KA-1274-20-21			25714.58	0.00	1446.21	1446.21	5
27	1615		39ABHPC3938R3Z8	Customer 5	ICHABE2021000504			18540.34	0.00	1968.33	1968.33	18
28
COMBINE TAXABLE VALUE

Cell Formulas
Range	Formula
J2:J15	J2	=IF(COUNTIFS(C$2:C2,C2,E$2:E2,E2,P$2:P2,P2)=1,H2,"Delete Row")
K2:K15	K2	=IF(COUNTIFS(C$2:C2,C2,E$2:E2,E2)=1,SUMIFS(J:J,C:C,C2,D:D,D2,E:E,E2),"Delete Row")
L2:L15	L2	=IF(COUNTIFS(C$2:C2,C2,E$2:E2,E2,P$2:P2,P2)=1,N2,"Delete Row")
M2:M15	M2	=IF(COUNTIFS(C$2:C2,C2,E$2:E2,E2)=1,SUMIFS(L:L,C:C,C2,D:D,D2,E:E,E2),"Delete Row")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E19:E27	Expression	=COUNTIFS(D$2:D$10,D19,E$2:E$10,E19)>1	text	NO
E2:E15	Expression	=COUNTIFS(D$2:D$15,D2,E$2:E$15,E2)>1	text	NO