How Can I Simplify the attached Formula -

[Houstonking]

Hello - How can i clean the below formula -

=SUM(COUNTIFS(State,{"CT","DE","PA","NJ","DC","MD","NH","OH"},Value,">10",OFFICE,"Name1"))+SUM(COUNTIFS(State,{"CT","DE","PA","NJ","DC","MD","NH","OH"},Value,">10",OFFICE,"Name2"))

Please help thanks

 

[Eric W]

There are several ways to shorten that formula, whether they are "cleaner" depends on your viewpoint.

	A	B	C
1	2	CT	Name1
2	2	DE	Name2
3	2	PA
4	2	NJ
5	2	DC
6		MD
7		NH
8		OH

<tbody>
</tbody>

Sheet12​

Worksheet Formulas Cell Formula A1 =SUM(COUNTIFS(State,{"CT","DE","PA","NJ","DC","MD","NH","OH"},Value,">10",Office,"Name1"))+SUM(COUNTIFS(State,{"CT","DE","PA","NJ","DC","MD","NH","OH"},Value,">10",Office,"Name2")) A2 =SUM(COUNTIFS(State,{"CT","DE","PA","NJ","DC","MD","NH","OH"},Value,">10",Office,{"Name1";"Name2"})) <tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula A3 {=SUM(COUNTIFS(State,B1:B8,Value,">10",Office,"Name1"))+SUM(COUNTIFS(State,B1:B8,Value,">10",Office,"Name2"))} A4 {=SUM(COUNTIFS(State,B1:B8,Value,">10",Office,{"Name1","Name2"}))} A5 {=SUM(COUNTIFS(State,B1:B8,Value,">10",Office,TRANSPOSE(C1:C2)))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

A1 is your original formula.

A2 combines the two sections into 1 by putting Name1 and Name2 into an array constant. Notice the semicolon in that array. When you use 2 array constants in a SUMIFS, one must be commas (columns), and one must be semicolons (rows).

A3 takes the state array and puts it into the sheet. You need Control+Shift+Enter to handle that.

A4 and A5 are combinations of those techniques. Note in A5 how TRANSPOSE is used, so that one array is columnwise, and the other is rowwise.

Hope this helps.