Need help to Combine 2 formulas

[RAJESH1960]

Hello experts

I am trying to combine 2 formulas and trying to apply to get the same result I used to get by using 2 different formulas. But I am getting an error.

=IF(COUNTIFS(C$2:C2,C2,E$2:E2,E2)=1,SUMIFS(K:K,C:C,C2,E:E,E2),"") to get the combined local tax

=IF(COUNTIFS(C$2:C10,C10,E$2:E10,E10)=1,SUMIFS(I:I,C:C,C10,E:E,E10),"") to get the combined interstate tax

As in every new data the range changes, I need to change the range every time. So, I tried to combine both the formulas with this formula

=IF(12=0,COUNTIFS(C$2:C2,C2,E$2:E2,E2)=1,SUMIFS(K:K,C:C,C2,E:E,E2),COUNTIFS(C$2:C2,C2,E$2:E2,E2)=1,SUMIFS(I:I,C:C,C2,E:E,E2)))

But I am not able to get the formula right.

query to combine tax IGST + CGST in one formula.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	a	b	c	d	e	f	g	h	Interstate	Combine Tax	Local
2	1		24AAHCS4768D4Z7		TAX/0488/21-22				473.88	0	0
3	2		24AAHCS4768D4Z7		TAX/0699/21-22				1269.9	0	0
4	3		24AAHCS4768D4Z7		TAX/0699/21-22				1401.83	FALSE	0
5	4		24AAHCS4768D4Z7		TAX/0715/21-22				338.64	0	0
6	5		24AAHCS4768D4Z7		TAX/0730/21-22				348.1	0	0
7	6		24AAHCS4768D4Z7		TAX/0789/21-22				1553.64	0	0
8	7		24AAHCS4768D4Z7		TAX/0789/21-22				437.52	FALSE	0
9	8		24AAHCS4768D4Z7		TAX/0798/21-22				480.27	0	0
10	9		24AAHCS4768D4Z7		TAX/0813/21-22				1058.25	0	0
11	10		24AAHCS4768D4Z7		TAX/0834/21-22				634.95	0	0
12	11		24AAHCS4768D4Z7		TAX/0834/21-22				527.63	FALSE	0
13	12		24AAHCS4768D4Z7		TAX/0900/21-22				479.19	0	0
14	13		24AAHCS4768D4Z7		TAX/0918/21-22				2716.8	0	0
15	14		24AAHCS4768D4Z7		TAX/0918/21-22				859.78	FALSE	0
16	15		29AAACT9430G1Z7		BLR7-981				0	6.55	6.55
17	16		29AAAFI7960D1ZB		RBR22E000721				0	6230	4.18
18	17		29AAAFI7960D1ZB		RBR22E000721				0	FALSE	4461.73
19	18		29AAAFI7960D1ZB		RBR22E000721				0	FALSE	1764.09
20	19		29AABFI4434R1ZU		KA01000321000306				0	726.38	336.64
21	20		29AABFI4434R1ZU		KA01000321000306				0	FALSE	389.74
22	21		29ABHPC1918R1Z8		ICHABE2122000044				0	155.71	0.24
23	22		29ABHPC1918R1Z8		ICHABE2122000044				0	FALSE	37.41
24	23		29ABHPC1918R1Z8		ICHABE2122000044				0	FALSE	118.06
25	24		29ADDFS6267R1Z1		0114/2021-22				0	74.17	74.17
26	25		29ADDFS6267R1Z1		0236/2021-22				0	389.65	9
27	26		29ADDFS6267R1Z1		0236/2021-22				0	FALSE	380.65
28	27		29ADDFS6267R1Z1		0244/2021-22				0	735.11	20.25
29	28		29ADDFS6267R1Z1		0244/2021-22				0	FALSE	714.86
30	29		29AGHPY0797M1ZB		107				0	2700	2700
31	30		29AGHPY0797M1ZB		118				0	2685.6	2685.6
32	31		29FZBPS6969D1ZO		277				0	2858.4	2858.4
33	32		29FZBPS6969D1ZO		281				0	2997	2997
34	33		29FZBPS6969D1ZO		284				0	3330	3330
35	34		29FZBPS6969D1ZO		285				0	2394	2394
36	35		06AXFPA2657R1Z5		8				3030	0	0
37	36		06BOVPG2947E1ZQ		08-2021-2022				1555.2	0	0
38	37		06BOVPG2947E1ZQ		08-2021-2022				2118	FALSE	0
39	38		09AAACI5853L2Z5		1812494132				2924.09	0	0
40	39		09AAACI5853L2Z5		1812603555				762.71	0	0
41	40		09AJHPM4274A1ZB		507				2100	0	0
42	41		09AJHPM4274A1ZB		508				1136	0	0
43	42		09AJHPM4274A1ZB		510				300	0	0
44	43		19AAACT9430G1Z8		SCCH-3083				13.1	0	0
Combine Tax

Cell Formulas
Range	Formula
J2:J44	J2	=IF(COUNTIFS(C$2:C2,C2,E$2:E2,E2)=1,SUMIFS(K:K,C:C,C2,E:E,E2),IF(12=0,IF(COUNTIFS(C$2:C2,C2,E$2:E2,E2)=1,SUMIFS(I:I,C:C,C2,E:E,E2))))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E2:E44	Expression	=COUNTIFS(C$2:C$19991,C2,E$2:E$19991,E2)>1	text	NO

 

[RAJESH1960]

=IF(12=0,COUNTIFS(C$2:C2,C2,E$2:E2,E2)=1,SUMIFS(K:K,C:C,C2,E:E,E2),COUNTIFS(C$2:C2,C2,E$2:E2,E2)=1,SUMIFS(I:I,C:C,C2,E:E,E2))))
even this is not working

 

[RAJESH1960]

query to combine tax IGST + CGST in one formula.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	a	b	c	d	e	f	g	h	combine Interstate	Interstate	Combine local	Local
2	15		29AAACT9430G1Z7		BLR7-981					6.55	6.55	6.55
3	16		29AAAFI7960D1ZB		RBR22E000721					6230	6230	4.18
4	17		29AAAFI7960D1ZB		RBR22E000721							4461.73
5	18		29AAAFI7960D1ZB		RBR22E000721							1764.09
6	19		29AABFI4434R1ZU		KA01000321000306					726.38	726.38	336.64
7	20		29AABFI4434R1ZU		KA01000321000306							389.74
8	21		29ABHPC1918R1Z8		ICHABE2122000044					155.71	155.71	0.24
9	22		29ABHPC1918R1Z8		ICHABE2122000044							37.41
10	23		29ABHPC1918R1Z8		ICHABE2122000044							118.06
11	24		29ADDFS6267R1Z1		0114/2021-22					74.17	74.17	74.17
12	25		29ADDFS6267R1Z1		0236/2021-22					389.65	389.65	9
13	26		29ADDFS6267R1Z1		0236/2021-22							380.65
14	27		29ADDFS6267R1Z1		0244/2021-22					735.11	735.11	20.25
15	28		29ADDFS6267R1Z1		0244/2021-22							714.86
16	29		29AGHPY0797M1ZB		107					2700	2700	2700
17	30		29AGHPY0797M1ZB		118					2685.6	2685.6	2685.6
18	31		29FZBPS6969D1ZO		277					2858.4	2858.4	2858.4
19	32		29FZBPS6969D1ZO		281					2997	2997	2997
20	33		29FZBPS6969D1ZO		284					3330	3330	3330
21	34		29FZBPS6969D1ZO		285					2394	2394	2394
22	43		19AAACT9430G1Z8		SCCH-3083				13.1	13.1	0	0
23	42		09AJHPM4274A1ZB		510				300	300	0	0
24	4		24AAHCS4768D4Z7		TAX/0715/21-22				338.64	338.64	0	0
25	5		24AAHCS4768D4Z7		TAX/0730/21-22				348.1	348.1	0	0
26	7		24AAHCS4768D4Z7		TAX/0789/21-22				1991.16	437.52	0	0
27	1		24AAHCS4768D4Z7		TAX/0488/21-22				473.88	473.88	0	0
28	12		24AAHCS4768D4Z7		TAX/0900/21-22				479.19	479.19	0	0
29	8		24AAHCS4768D4Z7		TAX/0798/21-22				480.27	480.27	0	0
30	11		24AAHCS4768D4Z7		TAX/0834/21-22				1162.58	527.63	0	0
31	10		24AAHCS4768D4Z7		TAX/0834/21-22					634.95		0
32	39		09AAACI5853L2Z5		1812603555				762.71	762.71	0	0
33	14		24AAHCS4768D4Z7		TAX/0918/21-22				3576.58	859.78	0	0
34	9		24AAHCS4768D4Z7		TAX/0813/21-22				1058.25	1058.25	0	0
35	41		09AJHPM4274A1ZB		508				1136	1136	0	0
36	2		24AAHCS4768D4Z7		TAX/0699/21-22				2671.73	1269.9	0	0
37	3		24AAHCS4768D4Z7		TAX/0699/21-22					1401.83		0
38	6		24AAHCS4768D4Z7		TAX/0789/21-22					1553.64		0
39	36		06BOVPG2947E1ZQ		08-2021-2022				3673.2	1555.2	0	0
40	40		09AJHPM4274A1ZB		507				2100	2100	0	0
41	37		06BOVPG2947E1ZQ		08-2021-2022					2118		0
42	13		24AAHCS4768D4Z7		TAX/0918/21-22					2716.8		0
43	38		09AAACI5853L2Z5		1812494132				2924.09	2924.09	0	0
44	35		06AXFPA2657R1Z5		8				3030	3030	0	0
Combine Tax

Cell Formulas
Range	Formula
J2:J21	J2	=IF(COUNTIFS(C$2:C2,C2,E$2:E2,E2)=1,SUMIFS(L:L,C:C,C2,E:E,E2),"")
I22:I44	I22	=IF(COUNTIFS(C$2:C22,C22,E$2:E22,E22)=1,SUMIFS(J:J,C:C,C22,E:E,E22),"")
K2:K44	K2	=IF(COUNTIFS(C$2:C2,C2,E$2:E2,E2)=1,SUMIFS(L:L,C:C,C2,E:E,E2),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E2:E44	Expression	=COUNTIFS(C$2:C$19991,C2,E$2:E$19991,E2)>1	text	NO

This is the right result in 2 different columns. This is how I use the formula part by part. I have to insert 2 additional columns to get the right result. The problem is I have to change the range every time in different data.

 

[Eric W]

I'm confused by what you mean by the range changing. I assume you add new rows at the bottom. The formulas just need to be copied down to the new rows. The ranges in the formulas will adapt appropriately. Consider this:

Book1 (version 1).xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	a	b	c	d	e	f	g	h	Interstate	Combine Tax	Local	Combined Local tax	Combined Interstate tax		Combined Tax
2	1		24AAHCS4768D4Z7		TAX/0488/21-22				473.88	473.88	0	0	473.88		473.88
3	2		24AAHCS4768D4Z7		TAX/0699/21-22				1269.9	2671.73	0	0	2671.73		2671.73
4	3		24AAHCS4768D4Z7		TAX/0699/21-22				1401.83		0
5	4		24AAHCS4768D4Z7		TAX/0715/21-22				338.64	338.64	0	0	338.64		338.64
6	5		24AAHCS4768D4Z7		TAX/0730/21-22				348.1	348.1	0	0	348.1		348.1
7	6		24AAHCS4768D4Z7		TAX/0789/21-22				1553.64	1991.16	0	0	1991.16		1991.16
8	7		24AAHCS4768D4Z7		TAX/0789/21-22				437.52		0
9	8		24AAHCS4768D4Z7		TAX/0798/21-22				480.27	480.27	0	0	480.27		480.27
10	9		24AAHCS4768D4Z7		TAX/0813/21-22				1058.25	1058.25	0	0	1058.25		1058.25
11	10		24AAHCS4768D4Z7		TAX/0834/21-22				634.95	1162.58	0	0	1162.58		1162.58
12	11		24AAHCS4768D4Z7		TAX/0834/21-22				527.63		0
13	12		24AAHCS4768D4Z7		TAX/0900/21-22				479.19	479.19	0	0	479.19		479.19
14	13		24AAHCS4768D4Z7		TAX/0918/21-22				2716.8	3576.58	0	0	3576.58		3576.58
15	14		24AAHCS4768D4Z7		TAX/0918/21-22				859.78		0
16	15		29AAACT9430G1Z7		BLR7-981				0	6.55	6.55	6.55	0		6.55
17	16		29AAAFI7960D1ZB		RBR22E000721				0	6230	4.18	6230	0		6230
18	17		29AAAFI7960D1ZB		RBR22E000721				0		4461.73
19	18		29AAAFI7960D1ZB		RBR22E000721				0		1764.09
20	19		29AABFI4434R1ZU		KA01000321000306				0	726.38	336.64	726.38	0		726.38
21	20		29AABFI4434R1ZU		KA01000321000306				0		389.74
22	21		29ABHPC1918R1Z8		ICHABE2122000044				0	155.71	0.24	155.71	0		155.71
23	22		29ABHPC1918R1Z8		ICHABE2122000044				0		37.41
24	23		29ABHPC1918R1Z8		ICHABE2122000044				0		118.06
25	24		29ADDFS6267R1Z1		0114/2021-22				0	74.17	74.17	74.17	0		74.17
26	25		29ADDFS6267R1Z1		0236/2021-22				0	389.65	9	389.65	0		389.65
27	26		29ADDFS6267R1Z1		0236/2021-22				0		380.65
28	27		29ADDFS6267R1Z1		0244/2021-22				0	735.11	20.25	735.11	0		735.11
29	28		29ADDFS6267R1Z1		0244/2021-22				0		714.86
30	29		29AGHPY0797M1ZB		107				0	2700	2700	2700	0		2700
31	30		29AGHPY0797M1ZB		118				0	2685.6	2685.6	2685.6	0		2685.6
32	31		29FZBPS6969D1ZO		277				0	2858.4	2858.4	2858.4	0		2858.4
33	32		29FZBPS6969D1ZO		281				0	2997	2997	2997	0		2997
34	33		29FZBPS6969D1ZO		284				0	3330	3330	3330	0		3330
35	34		29FZBPS6969D1ZO		285				0	2394	2394	2394	0		2394
36	35		06AXFPA2657R1Z5		8				3030	3030	0	0	3030		3030
37	36		06BOVPG2947E1ZQ		08-2021-2022				1555.2	3673.2	0	0	3673.2		3673.2
38	37		06BOVPG2947E1ZQ		08-2021-2022				2118		0
39	38		09AAACI5853L2Z5		1.81E+09				2924.09	2924.09	0	0	2924.09		2924.09
40	39		09AAACI5853L2Z5		1.81E+09				762.71	762.71	0	0	762.71		762.71
41	40		09AJHPM4274A1ZB		507				2100	2100	0	0	2100		2100
42	41		09AJHPM4274A1ZB		508				1136	1136	0	0	1136		1136
43	42		09AJHPM4274A1ZB		510				300	300	0	0	300		300
44	43		19AAACT9430G1Z8		SCCH-3083				13.1	13.1	0	0	13.1		13.1
Sheet34

Cell Formulas
Range	Formula
L2:L44	L2	=IF(COUNTIFS(C$2:C2,C2,E$2:E2,E2)=1,SUMIFS(K:K,C:C,C2,E:E,E2),"")
M2:M44	M2	=IF(COUNTIFS(C$2:C2,C2,E$2:E2,E2)=1,SUMIFS(I:I,C:C,C2,E:E,E2),"")
J2:J44	J2	=IF(L2<>"",L2+M2,"")
O2:O44	O2	=IF(COUNTIFS(C$2:C2,C2,E$2:E2,E2)=1,SUMIFS(K:K,C:C,C2,E:E,E2)+SUMIFS(I:I,C:C,C2,E:E,E2),"")

Here columns A:I and column K is the raw data. The formula in L2 is the combined local tax. The formula in M2 is the combined Interstate tax. To get the sum of them, you can just add those totals, which I did with the J2 formula. If you want to get the J2 total, without the L2 and M2 formulas, the combined version is in the O2 formula.

Let me know if this is what you want.

 

[Marcelo Branco]

See if this does what you need
J2 copied down
=IF(COUNTIFS(C$2:C2,C2,E$2:E2,E2)=1,IF(I2<>0,SUMIFS(I:I,C:C,C2,E:E,E2),SUMIFS(K:K,C:C,C2,E:E,E2)),"")

M.

 

[Marcelo Branco]

Simpler version
J2 copied down
=IF(COUNTIFS(C$2:C2,C2,E$2:E2,E2)=1,SUMIFS(IF(I2<>0,I:I,K:K),C:C,C2,E:E,E2),"")

M.

 

[RAJESH1960]

Simpler version
J2 copied down
=IF(COUNTIFS(C$2:C2,C2,E$2:E2,E2)=1,SUMIFS(IF(I2<>0,I:I,K:K),C:C,C2,E:E,E2),"")

M.

Both are working perfectly. Thanks once again Marcello Branco.

 

[Marcelo Branco]

You're welcome. Glad we could help.

M.

 

[RAJESH1960]

I'm confused by what you mean by the range changing. I assume you add new rows at the bottom. The formulas just need to be copied down to the new rows. The ranges in the formulas will adapt appropriately. Consider this:

Book1 (version 1).xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	a	b	c	d	e	f	g	h	Interstate	Combine Tax	Local	Combined Local tax	Combined Interstate tax		Combined Tax
2	1		24AAHCS4768D4Z7		TAX/0488/21-22				473.88	473.88	0	0	473.88		473.88
3	2		24AAHCS4768D4Z7		TAX/0699/21-22				1269.9	2671.73	0	0	2671.73		2671.73
4	3		24AAHCS4768D4Z7		TAX/0699/21-22				1401.83		0
5	4		24AAHCS4768D4Z7		TAX/0715/21-22				338.64	338.64	0	0	338.64		338.64
6	5		24AAHCS4768D4Z7		TAX/0730/21-22				348.1	348.1	0	0	348.1		348.1
7	6		24AAHCS4768D4Z7		TAX/0789/21-22				1553.64	1991.16	0	0	1991.16		1991.16
8	7		24AAHCS4768D4Z7		TAX/0789/21-22				437.52		0
9	8		24AAHCS4768D4Z7		TAX/0798/21-22				480.27	480.27	0	0	480.27		480.27
10	9		24AAHCS4768D4Z7		TAX/0813/21-22				1058.25	1058.25	0	0	1058.25		1058.25
11	10		24AAHCS4768D4Z7		TAX/0834/21-22				634.95	1162.58	0	0	1162.58		1162.58
12	11		24AAHCS4768D4Z7		TAX/0834/21-22				527.63		0
13	12		24AAHCS4768D4Z7		TAX/0900/21-22				479.19	479.19	0	0	479.19		479.19
14	13		24AAHCS4768D4Z7		TAX/0918/21-22				2716.8	3576.58	0	0	3576.58		3576.58
15	14		24AAHCS4768D4Z7		TAX/0918/21-22				859.78		0
16	15		29AAACT9430G1Z7		BLR7-981				0	6.55	6.55	6.55	0		6.55
17	16		29AAAFI7960D1ZB		RBR22E000721				0	6230	4.18	6230	0		6230
18	17		29AAAFI7960D1ZB		RBR22E000721				0		4461.73
19	18		29AAAFI7960D1ZB		RBR22E000721				0		1764.09
20	19		29AABFI4434R1ZU		KA01000321000306				0	726.38	336.64	726.38	0		726.38
21	20		29AABFI4434R1ZU		KA01000321000306				0		389.74
22	21		29ABHPC1918R1Z8		ICHABE2122000044				0	155.71	0.24	155.71	0		155.71
23	22		29ABHPC1918R1Z8		ICHABE2122000044				0		37.41
24	23		29ABHPC1918R1Z8		ICHABE2122000044				0		118.06
25	24		29ADDFS6267R1Z1		0114/2021-22				0	74.17	74.17	74.17	0		74.17
26	25		29ADDFS6267R1Z1		0236/2021-22				0	389.65	9	389.65	0		389.65
27	26		29ADDFS6267R1Z1		0236/2021-22				0		380.65
28	27		29ADDFS6267R1Z1		0244/2021-22				0	735.11	20.25	735.11	0		735.11
29	28		29ADDFS6267R1Z1		0244/2021-22				0		714.86
30	29		29AGHPY0797M1ZB		107				0	2700	2700	2700	0		2700
31	30		29AGHPY0797M1ZB		118				0	2685.6	2685.6	2685.6	0		2685.6
32	31		29FZBPS6969D1ZO		277				0	2858.4	2858.4	2858.4	0		2858.4
33	32		29FZBPS6969D1ZO		281				0	2997	2997	2997	0		2997
34	33		29FZBPS6969D1ZO		284				0	3330	3330	3330	0		3330
35	34		29FZBPS6969D1ZO		285				0	2394	2394	2394	0		2394
36	35		06AXFPA2657R1Z5		8				3030	3030	0	0	3030		3030
37	36		06BOVPG2947E1ZQ		08-2021-2022				1555.2	3673.2	0	0	3673.2		3673.2
38	37		06BOVPG2947E1ZQ		08-2021-2022				2118		0
39	38		09AAACI5853L2Z5		1.81E+09				2924.09	2924.09	0	0	2924.09		2924.09
40	39		09AAACI5853L2Z5		1.81E+09				762.71	762.71	0	0	762.71		762.71
41	40		09AJHPM4274A1ZB		507				2100	2100	0	0	2100		2100
42	41		09AJHPM4274A1ZB		508				1136	1136	0	0	1136		1136
43	42		09AJHPM4274A1ZB		510				300	300	0	0	300		300
44	43		19AAACT9430G1Z8		SCCH-3083				13.1	13.1	0	0	13.1		13.1
Sheet34

Cell Formulas
Range	Formula
L2:L44	L2	=IF(COUNTIFS(C$2:C2,C2,E$2:E2,E2)=1,SUMIFS(K:K,C:C,C2,E:E,E2),"")
M2:M44	M2	=IF(COUNTIFS(C$2:C2,C2,E$2:E2,E2)=1,SUMIFS(I:I,C:C,C2,E:E,E2),"")
J2:J44	J2	=IF(L2<>"",L2+M2,"")
O2:O44	O2	=IF(COUNTIFS(C$2:C2,C2,E$2:E2,E2)=1,SUMIFS(K:K,C:C,C2,E:E,E2)+SUMIFS(I:I,C:C,C2,E:E,E2),"")

Here columns A:I and column K is the raw data. The formula in L2 is the combined local tax. The formula in M2 is the combined Interstate tax. To get the sum of them, you can just add those totals, which I did with the J2 formula. If you want to get the J2 total, without the L2 and M2 formulas, the combined version is in the O2 formula.

Let me know if this is what you want.

Thanks for responding Eric W.