How Can I Simplify the attached Formula -

Houstonking

New Member
Joined
Jul 12, 2016
Messages
41
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
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
There are several ways to shorten that formula, whether they are "cleaner" depends on your viewpoint.

ABC
CTName1
DEName2
PA
NJ
DC
MD
NH
OH

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]2[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]2[/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]2[/TD]

[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

</tbody>
Sheet12

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]A1[/TH]
[TD="align: left"]=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"))[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]A2[/TH]
[TD="align: left"]=SUM(COUNTIFS(State,{"CT","DE","PA","NJ","DC","MD","NH","OH"},Value,">10",Office,{"Name1";"Name2"}))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]A3[/TH]
[TD="align: left"]{=SUM(COUNTIFS(State,B1:B8,Value,">10",Office,"Name1"))+SUM(COUNTIFS(State,B1:B8,Value,">10",Office,"Name2"))}[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]A4[/TH]
[TD="align: left"]{=SUM(COUNTIFS(State,B1:B8,Value,">10",Office,{"Name1","Name2"}))}[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]A5[/TH]
[TD="align: left"]{=SUM(COUNTIFS(State,B1:B8,Value,">10",Office,TRANSPOSE(C1:C2)))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]


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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,289
Members
452,631
Latest member
a_potato

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top