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]

I did find a way to do it in a single column (only done with the first one so far). As I suspected though, it is far more complex and inefficient.

I've set this formula to the exact ranges in the sample sheet, it could be done a bit bigger as required but full columns will take ages to calculate, that is assuming that excel doesn't crash under the load. I attempted the sample with full columns and had to force close excel because it was taking to long to process.

If you want to go with this method then dynamic named ranges (or structured tables) would be most sensible way to set the correct ranges for the formula to use. If you have a lot of data (1000's of rows) then I would strongly advise sticking with the extra column.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	A	B	TIN	NAME	Invoice number	C	D	Taxable Value (₹)	IGST	Combined Taxable	Combined Tax	CGST	SGST	Rate (%)
2	1524		39AKXPR3359E3ZJ	Customer 1	IN-ZSHB-37105			2159.32	0	2159.32	194.34	194.34	194.34	18
3	11		39AALCA3377L3Z6	Customer 2	SMN0083052002577			11863.56	0	11863.56	2135.44	1067.72	1067.72	18
4	338		39AALCA3377L3Z6	Customer 2	SMN0083052002577			11863.56	0	Delete Row	Delete Row	1067.72	1067.72	18
5	1645		34AAECB3833N3ZU	Customer 3	GST-530			26733.5	1336.68	26733.5	1336.68	0	0	5
6	86		39AACCC8357H3ZE	Customer 4	181/2020-21			1300	0	1300	117	117	117	18
7	1615		39ABHPC3938R3Z8	Customer 5	ICHABE2021000504			12480.5	0	18540.34	1968.33	1123.25	1123.25	18
8	1616		39ABHPC3938R3Z8	Customer 5	ICHABE2021000504			6031.27	0	Delete Row	Delete Row	844.37	844.37	28
9	1617		39ABHPC3938R3Z8	Customer 5	ICHABE2021000504			28.57	0	Delete Row	Delete Row	0.71	0.71	5
10	377		37AELPN4353H3ZC	Customer 6	IN-46253			1072.88	193.12	1072.88	386.24	0	0	18
11	68		37AELPN4353H3ZC	Customer 6	IN-46253			1072.88	193.12	Delete Row	Delete Row	0	0	18
12	618		39AAJFD5433B3ZR	Customer 7	KA-1176-20-21			51509.52	0	51509.52	1287.74	1287.74	1287.74	5
13	783		39AAJFD5433B3ZR	Customer 7	KA-1274-20-21			2761.9	0	25714.58	1446.21	69.05	69.05	5
14	784		39AAJFD5433B3ZR	Customer 7	KA-1274-20-21			22952.68	0	Delete Row	Delete Row	1377.16	1377.16	12
15	1782		37AAHPG6373H3ZO	Customer 8	2020-21-1243			26880	1344	26880	1344	0	0	5
Sheet1

Cell Formulas
Range	Formula
J2:J15	J2	=IF(COUNTIFS(C$2:C2,C2,E$2:E2,E2)=1,SUM(IFERROR(H$2:H$15/COUNTIFS(C$2:C$15,C$2:C$15,C$2:C$15,C2,E$2:E$15,E$2:E$15,E$2:E$15,E2,N$2:N$15,N$2:N$15),0)),"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")

 

[RAJESH1960]

I did find a way to do it in a single column (only done with the first one so far). As I suspected though, it is far more complex and inefficient.

I've set this formula to the exact ranges in the sample sheet, it could be done a bit bigger as required but full columns will take ages to calculate, that is assuming that excel doesn't crash under the load. I attempted the sample with full columns and had to force close excel because it was taking to long to process.

If you want to go with this method then dynamic named ranges (or structured tables) would be most sensible way to set the correct ranges for the formula to use. If you have a lot of data (1000's of rows) then I would strongly advise sticking with the extra column.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	A	B	TIN	NAME	Invoice number	C	D	Taxable Value (₹)	IGST	Combined Taxable	Combined Tax	CGST	SGST	Rate (%)
2	1524		39AKXPR3359E3ZJ	Customer 1	IN-ZSHB-37105			2159.32	0	2159.32	194.34	194.34	194.34	18
3	11		39AALCA3377L3Z6	Customer 2	SMN0083052002577			11863.56	0	11863.56	2135.44	1067.72	1067.72	18
4	338		39AALCA3377L3Z6	Customer 2	SMN0083052002577			11863.56	0	Delete Row	Delete Row	1067.72	1067.72	18
5	1645		34AAECB3833N3ZU	Customer 3	GST-530			26733.5	1336.68	26733.5	1336.68	0	0	5
6	86		39AACCC8357H3ZE	Customer 4	181/2020-21			1300	0	1300	117	117	117	18
7	1615		39ABHPC3938R3Z8	Customer 5	ICHABE2021000504			12480.5	0	18540.34	1968.33	1123.25	1123.25	18
8	1616		39ABHPC3938R3Z8	Customer 5	ICHABE2021000504			6031.27	0	Delete Row	Delete Row	844.37	844.37	28
9	1617		39ABHPC3938R3Z8	Customer 5	ICHABE2021000504			28.57	0	Delete Row	Delete Row	0.71	0.71	5
10	377		37AELPN4353H3ZC	Customer 6	IN-46253			1072.88	193.12	1072.88	386.24	0	0	18
11	68		37AELPN4353H3ZC	Customer 6	IN-46253			1072.88	193.12	Delete Row	Delete Row	0	0	18
12	618		39AAJFD5433B3ZR	Customer 7	KA-1176-20-21			51509.52	0	51509.52	1287.74	1287.74	1287.74	5
13	783		39AAJFD5433B3ZR	Customer 7	KA-1274-20-21			2761.9	0	25714.58	1446.21	69.05	69.05	5
14	784		39AAJFD5433B3ZR	Customer 7	KA-1274-20-21			22952.68	0	Delete Row	Delete Row	1377.16	1377.16	12
15	1782		37AAHPG6373H3ZO	Customer 8	2020-21-1243			26880	1344	26880	1344	0	0	5
Sheet1

Cell Formulas
Range	Formula
J2:J15	J2	=IF(COUNTIFS(C$2:C2,C2,E$2:E2,E2)=1,SUM(IFERROR(H$2:H$15/COUNTIFS(C$2:C$15,C$2:C$15,C$2:C$15,C2,E$2:E$15,E$2:E$15,E$2:E$15,E2,N$2:N$15,N$2:N$15),0)),"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")

Perfect. This is what I was talking about. My data has up to 20000 rows in some cases. With the help of this forum, I have used more complex formulas in different cases. I tried to make it as simple as possible so that I can write a code for my project easily. First formulas and then VBA code. A long way to go. Thanks JasonB75

 

[RAJESH1960]

I did find a way to do it in a single column (only done with the first one so far). As I suspected though, it is far more complex and inefficient.

I've set this formula to the exact ranges in the sample sheet, it could be done a bit bigger as required but full columns will take ages to calculate, that is assuming that excel doesn't crash under the load. I attempted the sample with full columns and had to force close excel because it was taking to long to process.

If you want to go with this method then dynamic named ranges (or structured tables) would be most sensible way to set the correct ranges for the formula to use. If you have a lot of data (1000's of rows) then I would strongly advise sticking with the extra column.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	A	B	TIN	NAME	Invoice number	C	D	Taxable Value (₹)	IGST	Combined Taxable	Combined Tax	CGST	SGST	Rate (%)
2	1524		39AKXPR3359E3ZJ	Customer 1	IN-ZSHB-37105			2159.32	0	2159.32	194.34	194.34	194.34	18
3	11		39AALCA3377L3Z6	Customer 2	SMN0083052002577			11863.56	0	11863.56	2135.44	1067.72	1067.72	18
4	338		39AALCA3377L3Z6	Customer 2	SMN0083052002577			11863.56	0	Delete Row	Delete Row	1067.72	1067.72	18
5	1645		34AAECB3833N3ZU	Customer 3	GST-530			26733.5	1336.68	26733.5	1336.68	0	0	5
6	86		39AACCC8357H3ZE	Customer 4	181/2020-21			1300	0	1300	117	117	117	18
7	1615		39ABHPC3938R3Z8	Customer 5	ICHABE2021000504			12480.5	0	18540.34	1968.33	1123.25	1123.25	18
8	1616		39ABHPC3938R3Z8	Customer 5	ICHABE2021000504			6031.27	0	Delete Row	Delete Row	844.37	844.37	28
9	1617		39ABHPC3938R3Z8	Customer 5	ICHABE2021000504			28.57	0	Delete Row	Delete Row	0.71	0.71	5
10	377		37AELPN4353H3ZC	Customer 6	IN-46253			1072.88	193.12	1072.88	386.24	0	0	18
11	68		37AELPN4353H3ZC	Customer 6	IN-46253			1072.88	193.12	Delete Row	Delete Row	0	0	18
12	618		39AAJFD5433B3ZR	Customer 7	KA-1176-20-21			51509.52	0	51509.52	1287.74	1287.74	1287.74	5
13	783		39AAJFD5433B3ZR	Customer 7	KA-1274-20-21			2761.9	0	25714.58	1446.21	69.05	69.05	5
14	784		39AAJFD5433B3ZR	Customer 7	KA-1274-20-21			22952.68	0	Delete Row	Delete Row	1377.16	1377.16	12
15	1782		37AAHPG6373H3ZO	Customer 8	2020-21-1243			26880	1344	26880	1344	0	0	5
Sheet1

Cell Formulas
Range	Formula
J2:J15	J2	=IF(COUNTIFS(C$2:C2,C2,E$2:E2,E2)=1,SUM(IFERROR(H$2:H$15/COUNTIFS(C$2:C$15,C$2:C$15,C$2:C$15,C2,E$2:E$15,E$2:E$15,E$2:E$15,E2,N$2:N$15,N$2:N$15),0)),"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")

The K2 formula needs editing as it is taking both the common values as per rate. I think I can alter that.

 

[RAJESH1960]

Jasonb75. It's not working in the original data base. The common taxable and common tax amounts are showing 1/2 of the total amounts where the rate is the same. FYI I have changed the range in the formula. GTG. Will try again tonight.

 

[RAJESH1960]

query to combine tax.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Line	A	B	C	D	E	GROSS	TAXABLE	IGST	Combine TAXABLE	Combine CGST	CGST	SGST	Rate	Correct Answer to be in J	Correct Answer to be in K
2	1		39AKXPR1159E3ZJ	Customer 1	IN-ZSHB-37105		2548.00	2159.32	0.00	2159.32	194.34	194.34		18	2159.32	194.34
3	2		39AALCA3177L1Z7	Customer 2	SMN0083052002577		13999.00	11863.56	0.00	5931.78	533.86	1067.72		18	11863.56	1067.72
4	3		39AALCA3177L1Z7	Customer 2	SMN0083052002577		13999.00	11863.56	0.00	Delete Row	Delete Row	1067.72		18	Delete Row	Delete Row
5	4		39AAICA3918J1ZE	Customer 3	ADS-2021-20681		1731.77	1467.61	0.00	1467.61	132.08	132.08		18	1467.61	132.08
6	5		39AAMCA7514L1Z8	Customer 4	239		745.00	631.36	0.00	315.68	28.41	56.82		18	631.36	56.82
7	6		39AAMCA7514L1Z8	Customer 4	239		745.00	631.36	0.00	Delete Row	Delete Row	56.82		18	Delete Row	Delete Row
8	7		34AAECB3813N1ZU	Customer 5	GST-448		65244.00	62136.75	3106.84	62136.75	0.00	0.00		5	62136.75	0.00
9	8		34AAECB3813N1ZU	Customer 5	GST-46		47966.00	45681.75	2284.09	22840.88	0.00	0.00		5	45681.75	0.00
10	9		34AAECB3813N1ZU	Customer 5	GST-46		47966.00	45681.75	2284.09	Delete Row	Delete Row	0.00		5	Delete Row	Delete Row
11	10		17AAHPG7373H1ZO	Customer 6	2020-21-1243		28224.00	26880.00	1344.00	26880.00	0.00	0.00		5	26880.00	0.00
12	11		17ATLPK9833D1ZK	Customer 7	214		34965.00	33300.00	1665.00	16650.00	0.00	0.00		5	33300.00	0.00
13	12		17ATLPK9833D1ZK	Customer 7	214		34965.00	33300.00	1665.00	Delete Row	Delete Row	0.00		5	Delete Row	Delete Row
14	13		17ATLPK9833D1ZK	Customer 7	284		32340.00	30800.00	1540.00	30800.00	0.00	0.00		5	30800.00	0.00
15	14		39AADCF1911H1ZU	Customer 8	135		5890.00	5610.00	0.00	2805.00	70.13	140.25		5	5610.00	140.25
16	15		39AADCF1911H1ZU	Customer 8	135		5890.00	5610.00	0.00	Delete Row	Delete Row	140.25		5	Delete Row	Delete Row
17	16		39AADCF1911H1ZU	Customer 8	140		13497.00	12854.50	0.00	12854.50	321.35	321.35		5	12854.50	321.35
18	17		39AADFG7717M1ZY	Customer 9	KR-POS-1612		36.00	30.00	0.00	30.00	2.70	2.70		18	30.00	2.70
19	18		39AADFG7717M1ZY	Customer 9	KR-POS-1718		608.00	443.10	0.00	443.10	26.59	26.59		12	537.10	35.05
20	19		39AADFG7717M1ZY	Customer 9	KR-POS-1718		608.00	94.00	0.00	Delete Row	Delete Row	8.46		18	Delete Row	Delete Row
21	20		39AADFG7717M1ZY	Customer 9	KR-POS-1719		759.00	677.00	0.00	677.00	40.62	40.62		12	677.00	40.62
22	21		39AADFG7717M1ZY	Customer 9	KR-POS-1758		90.00	30.00	0.00	30.00	1.80	1.80		12	77.00	6.03
23	22		39AADFG7717M1ZY	Customer 9	KR-POS-1758		90.00	47.00	0.00	Delete Row	Delete Row	4.23		18	Delete Row	Delete Row
24	23		39AADFG7717M1ZY	Customer 9	KR-POS-2183		1064.00	950.00	0.00	950.00	57.00	57.00		12	950.00	57.00
25	24		39AADFG7717M1ZY	Customer 9	KR-POS-2302		599.00	171.00	0.00	171.00	10.26	10.26		12	516.00	41.31
26	25		39AADFG7717M1ZY	Customer 9	KR-POS-2302		599.00	345.00	0.00	Delete Row	Delete Row	31.05		18	Delete Row	Delete Row
27	26		39AADFG7717M1ZY	Customer 9	KR-POS-2567		1476.00	1149.12	0.00	1149.12	68.95	68.95		12	1309.12	83.35
28	27		39AADFG7717M1ZY	Customer 9	KR-POS-2567		1476.00	160.00	0.00	Delete Row	Delete Row	14.40		18	Delete Row	Delete Row
29	28		39AADFG7717M1ZY	Customer 9	KR-POS-261		2787.00	2319.80	0.00	2319.80	139.19	139.19		12	2479.80	153.59
30	29		39AADFG7717M1ZY	Customer 9	KR-POS-261		2787.00	160.00	0.00	Delete Row	Delete Row	14.40		18	Delete Row	Delete Row
31	30		39AADFG7717M1ZY	Customer 9	KR-POS-2758		1576.00	1354.36	0.00	1354.36	81.26	81.26		12	1404.36	85.76
32	31		39AADFG7717M1ZY	Customer 9	KR-POS-2758		1576.00	50.00	0.00	Delete Row	Delete Row	4.50		18	Delete Row	Delete Row
33	32		17AAACH1743R1ZF	Customer 10	2020-21-7138		87091.20	82944.00	4147.20	82944.00	0.00	0.00		5	83827.92
34	33		34ACNPT1585G1ZB	Customer 11	62		1043.00	883.92	159.11	441.96	0.00	0.00		18	883.92	0.00
35	34		34ACNPT1585G1ZB	Customer 11	62		1043.00	883.92	159.11	Delete Row	Delete Row	0.00		18	Delete Row	Delete Row
36	35		39AADCH8879C1Z5	Customer 12	AD/647554		728.95	617.75	0.00	617.75	55.60	55.60		18	617.75	55.60
Taxable + Tax

Cell Formulas
Range	Formula
J2:J36	J2	=IF(COUNTIFS(C$2:C2,C2,E$2:E2,E2)=1,SUM(IFERROR(H$2:H$18296/COUNTIFS(C$2:C$18296,C$2:C$18296,C$2:C$18296,C2,E$2:E$18296,E$2:E$18296,E$2:E$18296,E2,N$2:N$18296,N$2:N$18296),0)),"Delete Row")
K2:K36	K2	=IF(COUNTIFS(C$2:C2,C2,E$2:E2,E2)=1,SUM(IFERROR(L$2:L$18296/COUNTIFS(C$2:C$18296,C$2:C$18296,C$2:C$18296,C2,E$2:E$18296,E$2:E$18296,E$2:E$18296,E2,N$2:N$18296,N$2:N$18296),0)),"Delete Row")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E2:E36	Expression	=COUNTIFS(D$2:D$18296,D2,E$2:E$18296,E2)>1	text	NO

 

[jasonb75]

I forgot to add that you will need to array confirm the formula in the first row with Ctrl Shift Enter before filling down.

 

[RAJESH1960]

Yo

I did find a way to do it in a single column (only done with the first one so far). As I suspected though, it is far more complex and inefficient.

I've set this formula to the exact ranges in the sample sheet, it could be done a bit bigger as required but full columns will take ages to calculate, that is assuming that excel doesn't crash under the load. I attempted the sample with full columns and had to force close excel because it was taking to long to process.

If you want to go with this method then dynamic named ranges (or structured tables) would be most sensible way to set the correct ranges for the formula to use. If you have a lot of data (1000's of rows) then I would strongly advise sticking with the extra column.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	A	B	TIN	NAME	Invoice number	C	D	Taxable Value (₹)	IGST	Combined Taxable	Combined Tax	CGST	SGST	Rate (%)
2	1524		39AKXPR3359E3ZJ	Customer 1	IN-ZSHB-37105			2159.32	0	2159.32	194.34	194.34	194.34	18
3	11		39AALCA3377L3Z6	Customer 2	SMN0083052002577			11863.56	0	11863.56	2135.44	1067.72	1067.72	18
4	338		39AALCA3377L3Z6	Customer 2	SMN0083052002577			11863.56	0	Delete Row	Delete Row	1067.72	1067.72	18
5	1645		34AAECB3833N3ZU	Customer 3	GST-530			26733.5	1336.68	26733.5	1336.68	0	0	5
6	86		39AACCC8357H3ZE	Customer 4	181/2020-21			1300	0	1300	117	117	117	18
7	1615		39ABHPC3938R3Z8	Customer 5	ICHABE2021000504			12480.5	0	18540.34	1968.33	1123.25	1123.25	18
8	1616		39ABHPC3938R3Z8	Customer 5	ICHABE2021000504			6031.27	0	Delete Row	Delete Row	844.37	844.37	28
9	1617		39ABHPC3938R3Z8	Customer 5	ICHABE2021000504			28.57	0	Delete Row	Delete Row	0.71	0.71	5
10	377		37AELPN4353H3ZC	Customer 6	IN-46253			1072.88	193.12	1072.88	386.24	0	0	18
11	68		37AELPN4353H3ZC	Customer 6	IN-46253			1072.88	193.12	Delete Row	Delete Row	0	0	18
12	618		39AAJFD5433B3ZR	Customer 7	KA-1176-20-21			51509.52	0	51509.52	1287.74	1287.74	1287.74	5
13	783		39AAJFD5433B3ZR	Customer 7	KA-1274-20-21			2761.9	0	25714.58	1446.21	69.05	69.05	5
14	784		39AAJFD5433B3ZR	Customer 7	KA-1274-20-21			22952.68	0	Delete Row	Delete Row	1377.16	1377.16	12
15	1782		37AAHPG6373H3ZO	Customer 8	2020-21-1243			26880	1344	26880	1344	0	0	5
Sheet1

Cell Formulas
Range	Formula
J2:J15	J2	=IF(COUNTIFS(C$2:C2,C2,E$2:E2,E2)=1,SUM(IFERROR(H$2:H$15/COUNTIFS(C$2:C$15,C$2:C$15,C$2:C$15,C2,E$2:E$15,E$2:E$15,E$2:E$15,E2,N$2:N$15,N$2:N$15),0)),"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")

You are right Jasonb. Now I understand what you tried to tell me. If it is taking so much time to calculate in these 30-40 rows, I can imagine how long will it take to calculate in 20000 rows. Till I find a better solution, I think I will go with the 4 helper columns as it is faster. Thank you so much for your time and effort.

 

[RAJESH1960]

Even the above working formula with the helper columns is not working in the data base. Better start fresh tomorrow and call it off for today.

 

[jasonb75]

As long as the format is the same it will work fine. Possibly you have one of the row / column references wrong when you've changed it to fit the database.

 

[RAJESH1960]

As long as the format is the same it will work fine. Possibly you have one of the row / column references wrong when you've changed it to fit the database.

I have arranged the columns in the same order as in the data base. That is why I have left some blank columns so that I can copy the formula and paste it directly. There is one mismatch only to correct. Will let you know as soon I find the mistake.