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:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Region 1[/TD]
[TD]APAC[/TD]
[TD]Region 2[/TD]
[TD]US[/TD]
[TD]Region 3[/TD]
[TD]EMEA[/TD]
[/TR]
[TR]
[TD]Country 1[/TD]
[TD]Japan[/TD]
[TD]Country 1[/TD]
[TD]Florida[/TD]
[TD]Country 1[/TD]
[TD]Greece[/TD]
[/TR]
[TR]
[TD]Country 2[/TD]
[TD][/TD]
[TD]Country 2[/TD]
[TD]California[/TD]
[TD]Country 2[/TD]
[TD]Sweden[/TD]
[/TR]
[TR]
[TD]Country 3[/TD]
[TD][/TD]
[TD]Country 3[/TD]
[TD]Texas[/TD]
[TD]Country 3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Country 4[/TD]
[TD][/TD]
[TD]Country 4[/TD]
[TD]Utah[/TD]
[TD]Country 4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Country 5[/TD]
[TD][/TD]
[TD]Country 5[/TD]
[TD][/TD]
[TD]Country 5[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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.

​

​