Excel formula is exceeding cell length parameters

[shre0047]

I have a manual table which will be updated with new countries/states every 6 months or so. Once we update the countries/states, it will auto update a dashboard and the formulas associated with it.

Example:

Region 1	APAC	Region 2	US	Region 3	EMEA
Country 1	Japan	Country 1	Florida	Country 1	Greece
Country 2		Country 2	California	Country 2	Sweden
Country 3		Country 3	Texas	Country 3
Country 4		Country 4	Utah	Country 4
Country 5		Country 5		Country 5

<tbody>
</tbody>

Dashboard formula example for one cell:
=IF($A$2="EMEA",
COUNTIFS('DATA'!$E:$E,Parameters!F2,'DATA'!$C:$C,$A$4)
+COUNTIFS('DATA'!$E:$E,Parameters!F3,'DATA'!$C:$C,$A$4)
+COUNTIFS('DATA'!$E:$E,Parameters!F4,'DATA'!$C:$C,$A$4)
+COUNTIFS('DATA'!$E:$E,Parameters!F5,'DATA'!$C:$C,$A$4)
+COUNTIFS('DATA'!$E:$E,Parameters!F6,'DATA'!$C:$C,$A$4),
IF($A$2="US",
COUNTIFS('DATA'!$E:$E,Parameters!D2,'DATA'!$C:$C,$A$4)
+COUNTIFS('DATA'!$E:$E,Parameters!D3,'DATA'!$C:$C,$A$4)
+COUNTIFS('DATA'!$E:$E,Parameters!D4,'DATA'!$C:$C,$A$4)
+COUNTIFS('DATA'!$E:$E,Parameters!D5,'DATA'!$C:$C,$A$4)
+COUNTIFS('DATA'!$E:$E,Parameters!D6,'DATA'!$C:$C,$A$4),
IF($A$2="APAC",
COUNTIFS('DATA'!$E:$E,Parameters!B2,'DATA'!$C:$C,$A$4)
+COUNTIFS('DATA'!$E:$E,Parameters!B3,'DATA'!$C:$C,$A$4)
+COUNTIFS('DATA'!$E:$E,Parameters!B4,'DATA'!$C:$C,$A$4)
+COUNTIFS('DATA'!$E:$E,Parameters!B5,'DATA'!$C:$C,$A$4)
+COUNTIFS('DATA'!$E:$E,Parameters!B6,'DATA'!$C:$C,$A$4),
)))

My plan is to having 15-20 rows of countries listed out and added to the formula. However, I expect to run into a limit of number of characters I can add to a cell. Is there a way around this?

 

[Kamolga]

The easiest way is to select

'DATA'!$E:$E, click formula tab and define name. You name it Rng1. Same with

'DATA'!$C:$C that you call Rng2. You can then replace

[LEFT][COLOR=#333333][FONT=Verdana]=IF($A$2="EMEA",[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]COUNTIFS('DATA'!$E:$E,Parameters!F2,'DATA'!$C:$C,$A$4)[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]+COUNTIFS('DATA'!$E:$E,Parameters!F3,'DATA'!$C:$C,$A$4)[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]+COUNTIFS('DATA'!$E:$E,Parameters!F4,'DATA'!$C:$C,$A$4)[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]+COUNTIFS('DATA'!$E:$E,Parameters!F5,'DATA'!$C:$C,$A$4)[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]+COUNTIFS('DATA'!$E:$E,Parameters!F6,'DATA'!$C:$C,$A$4),[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]IF($A$2="US",[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]COUNTIFS('DATA'!$E:$E,Parameters!D2,'DATA'!$C:$C,$A$4)[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]+COUNTIFS('DATA'!$E:$E,Parameters!D3,'DATA'!$C:$C,$A$4)[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]+COUNTIFS('DATA'!$E:$E,Parameters!D4,'DATA'!$C:$C,$A$4)[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]+COUNTIFS('DATA'!$E:$E,Parameters!D5,'DATA'!$C:$C,$A$4)[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]+COUNTIFS('DATA'!$E:$E,Parameters!D6,'DATA'!$C:$C,$A$4),[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]IF($A$2="APAC",[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]COUNTIFS('DATA'!$E:$E,Parameters!B2,'DATA'!$C:$C,$A$4)[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]+COUNTIFS('DATA'!$E:$E,Parameters!B3,'DATA'!$C:$C,$A$4)[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]+COUNTIFS('DATA'!$E:$E,Parameters!B4,'DATA'!$C:$C,$A$4)[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]+COUNTIFS('DATA'!$E:$E,Parameters!B5,'DATA'!$C:$C,$A$4)[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]+COUNTIFS('DATA'!$E:$E,Parameters!B6,'DATA'!$C:$C,$A$4),[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana])))[/FONT][/COLOR][/LEFT]

by

[COLOR=#333333][FONT=Verdana]=IF($A$2="EMEA",[/FONT][/COLOR]
[FONT=Calibri][SIZE=3][COLOR=#000000]COUNTIFS(Rng1,Parameters!F2,Rng2,[COLOR=#333333][FONT=Verdana]$A$4[/FONT][/COLOR])
+COUNTIFS(Rng1,Parameters!F3,Rng2[COLOR=#000000][FONT=Calibri],[/FONT][/COLOR][COLOR=#333333][FONT=Verdana]$A$4[/FONT][/COLOR])
+COUNTIFS(Rng1,Parameters!F4,Rng2[COLOR=#000000][FONT=Calibri],[/FONT][/COLOR][COLOR=#333333][FONT=Verdana]$A$4[/FONT][/COLOR])
+COUNTIFS(Rng1,Parameters!F5,Rng2[COLOR=#000000][FONT=Calibri],[/FONT][/COLOR][COLOR=#333333][FONT=Verdana]$A$4[/FONT][/COLOR])
+COUNTIFS(Rng1,Parameters!F6,Rng2[COLOR=#000000][FONT=Calibri],[/FONT][/COLOR][COLOR=#333333][FONT=Verdana]$A$4[/FONT][/COLOR]),
IF($A$2="US",
COUNTIFS(Rng1,Parameters!D2,Rng2[COLOR=#000000][FONT=Calibri],[/FONT][/COLOR][COLOR=#333333][FONT=Verdana]$A$4[/FONT][/COLOR])
+COUNTIFS(Rng1,Parameters!D3,Rng2[COLOR=#000000][FONT=Calibri],[/FONT][/COLOR][COLOR=#333333][FONT=Verdana]$A$4[/FONT][/COLOR])
+COUNTIFS(Rng1,Parameters!D4,Rng2[COLOR=#000000][FONT=Calibri],[/FONT][/COLOR][COLOR=#333333][FONT=Verdana]$A$4[/FONT][/COLOR])
+COUNTIFS(Rng1,Parameters!D5,Rng2[COLOR=#000000][FONT=Calibri],[/FONT][/COLOR][COLOR=#333333][FONT=Verdana]$A$4[/FONT][/COLOR])
+COUNTIFS(Rng1,Parameters!D6,Rng2[COLOR=#000000][FONT=Calibri],[/FONT][/COLOR][COLOR=#333333][FONT=Verdana]$A$4[/FONT][/COLOR]),
IF($A$2="APAC",
COUNTIFS(Rng1,Parameters!B2,Rng2[COLOR=#000000][FONT=Calibri],[/FONT][/COLOR][COLOR=#333333][FONT=Verdana]$A$4[/FONT][/COLOR])
+COUNTIFS(Rng1,Parameters!B3,Rng2[COLOR=#000000][FONT=Calibri],[/FONT][/COLOR][COLOR=#333333][FONT=Verdana]$A$4[/FONT][/COLOR])
+COUNTIFS(Rng1,Parameters!B4,Rng2[COLOR=#000000][FONT=Calibri],[/FONT][/COLOR][COLOR=#333333][FONT=Verdana]$A$4[/FONT][/COLOR])
+COUNTIFS(Rng1,Parameters!B5,Rng2[COLOR=#000000][FONT=Calibri],[/FONT][/COLOR][COLOR=#333333][FONT=Verdana]$A$4[/FONT][/COLOR])
+COUNTIFS(Rng1,Parameters!B6,Rng2[COLOR=#000000][FONT=Calibri],[/FONT][/COLOR][COLOR=#333333][FONT=Verdana]$A$4[/FONT][/COLOR]),
)))[/COLOR][/SIZE][/FONT]

You can apply same principle to $A$4, parameters!F2, etc.

​

​