SUMIFS getting too big - is there a better way?

[InstructorAmberA]

Situation - there is a tally sheet where an employee tallies things that are missing based on States and Trucks run in a state. Each State "owns" a cell in the Sheet.

If the Full State is missing, the employee types the State abbreviation in that State's cell. If a partial number is missing, they enter the Qty of trucks missing. I made a formula for one district. The formula is ugly and I am sure there must be a better way but I am just learning Excel. My current formula works as expected and can be used as a base if needed.

Current Need: The current request is to extend from Columns M:AD to M:CZ Currently I am typing "=SUMIFS($AR$10:AR$47,AP$10:AP$47,M10)+" for every cell in the row. I don't want to add this 40 more times .

The current SUMIFS lists each cell M10-AC10, uses TotalTruckNumber if an abbreviation Text is entered, and then takes the full row to collect the "numbers' to sum the full row.
TotalTruckNumber returned for the squares come from a Table currently housed in AP10:AR47

Partial Example shown here:

Row/column	AP	AQ	AR
10		State	Owner(s)	Total Trucks for State
11		AR	1	3
12		CA	5	18
13		IA	1	2
14		IN	1	6
15		KY	1	5
16		MI	1	1

<colgroup><col><col span="3"><col></colgroup><tbody>
</tbody>

Example Tally Sheet below. Cell K9=FoodTruckCount Text. K10 is the location of the formula.
-L- is a blank column. AR is Cell M10

	K -L-	M	N	O	P	Q
row9	Food Truck Count	AR	CA	IA	IN	KY
row 10	8	AR	2	IA	1
row 11	0
row 12	43	AR	CA	IA	IN	KY

<colgroup><col span="3"><col><col span="3"></colgroup><tbody>
</tbody>

Current working formula being used is :
=SUMIFS($AR$10:AR$47,AP$10:AP$47,M10)+SUMIFS($AR$10:$AR$47,$AP$10:$AP$47,N10)+SUMIFS($AR$10:$AR$47,$AP$10:$AP$47,O10)+SUMIFS($AR$10:$AR$47,$AP$10:$AP$47,P10)+SUMIFS($AR$10:$AR$47,$AP$10:$AP$47,Q10)+SUMIFS($AR$10:$AR$47,$AP$10:$AP$47,R10)+SUMIFS($AR$10:$AR$47,$AP$10:$AP$47,S10)+SUMIFS($AR$10:$AR$47,$AP$10:$AP$47,T10)+SUMIFS($AR$10:$AR$47,$AP$10:$AP$47,U10)+SUMIFS($AR$10:$AR$47,$AP$10:$AP$47,V10)+SUMIFS($AR$10:$AR$47,$AP$10:$AP$47,W10)+SUMIFS($AR$10:$AR$47,$AP$10:$AP$47,X10)+SUMIFS($AR$10:$AR$47,$AP$10:$AP$47,Y10)+SUMIFS($AR$10:$AR$47,$AP$10:$AP$47,Z10)+SUMIFS($AR$10:$AR$47,$AP$10:$AP$47,AA10)+SUMIFS($AR$10:$AR$47,$AP$10:$AP$47,AB10)+SUMIFS($AR$10:$AR$47,$AP$10:$AP$47,AC10)+SUMIFS($AR$10:$AR$47,$AP$10:$AP$47,AD10)+SUM(M10:AC10)

The tally sheet being horizontal is what seems to be killing me. I can't seem to find the right formula combination to read each square individually in the horizontal format. Any suggestions are welcome if a complete rework would be better. The users of the form have been doing this manually and aren't happy about me automating it but I can force the issue if any of you have suggestions that would be more productive. See ** question below

(Total Trucks per State can be listed in Row 8 if it would be helpful. )

Info extras if needed: This sheet tallies items such as forms, emails, schedules etc and is a revolving list of "did you get X from each owner for each truck?" to become "This" is missing - track it down list." Unlike an attendance sheet, the lines are continuously being added. Each line item gets used for multiple reports.

**I am almost certainly missing an easy solution but have been considering copying all of the cells to another sheet with a vlookup of sorts (hidden from the users "stuck" on this form) and making it vertical, then finding the solutions on that page, returning it to the one shown here. Thoughts on that? If there is an easy formula, it would be easier to leave it like it is.

Thanks in advance!

 

[Eric W]

Try

=SUM(SUMIFS($AR$10:$AR$47,AP$10:AP$47,M10:AD10))+SUM(M10:AC10)

and confirm your formula by pressing Control+Shift+Enter instead of just enter.

 

[jtakw]

Hi,

It was a bit difficult for me to follow your post, but you can setup your formula like this instead of the multiple SUMIFS, Please check the ranges to make sure it matches your data:

Book1
	J
8	0
Sheet355

Cell Formulas
Range	Formula
J8	=SUMPRODUCT((AP$10:AP$47=M10:CZ10)*$AR$10:$AR$47)+SUM(M10:CZ10)

 

[InstructorAmberA]

@jtakw and @Erik W Thank you both so much! Both of these worked great!

Quick followup questions

1) If choosing between the 2, is there a reason to choose one over the other for long term use?
2) Can you name/define what these processes would be called so I can go watch some videos about them for a more in depth understanding? I somehow missed this formula in all my searching this past week.

Thanks again for the quick and very accurate responses!

Hi,

It was a bit difficult for me to follow your post, but you can setup your formula like this instead of the multiple SUMIFS, Please check the ranges to make sure it matches your data:

<table cellpadding="2.5px" rules="all" verdana,="" arial,="" tahoma,="" calibri,="" geneva,="" sans-serif;="" border:="" 1px="" solid="" rgb(187,="" 187,="" 187);="" border-collapse:="" collapse;"="" width=""><colgroup><col><col></colgroup><thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH][/TH]
[TH]J[/TH]
[/TR]
</thead><tbody>[TR]
[TD="align: center"]8[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody></table>

Sheet355​

<table cellpadding="2.5px" rules="all" style="width: 85%" width="85%"><tbody>[TR]
[TD]Worksheet Formulas<table cellpadding="2.5px" rules="all" style="width: 100%" width="100%"><thead><tr style="background-color: rgb(218, 231, 245); color: rgb(22, 17, 32);">[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
</tr></thead><tbody><tr>[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J8[/TH]
[TD]=SUMPRODUCT((AP$10:AP$47=M10:CZ10)*$AR$10:$AR$47)+SUM(M10:CZ10)[/TD]
</tr></tbody></table>[/TD]
[/TR]
</tbody></table>

Try

=SUM(SUMIFS($AR$10:$AR$47,AP$10:AP$47,M10:AD10))+SUM(M10:AC10)

 

[Eric W]

A lot of times it's really hard to say which formula is "best". It depends on a lot of factors, many of which are specific to you or your application. As far as efficiency goes, mine is a tiny bit faster, but by so little we can discount it, unless you have a lot of these formulas. So what it really comes down to is which one can you better understand, and better maintain?

In my formula, it calculates the SUMIFS once for every value in M10:AD10, saves the results in a 1-D array, then uses the SUM to add up the intermediate results. But it is an array formula, requiring CSE, which some people avoid.

jtakw's formula creates a 2-D array (the red part) which compares every value in AP10:AP47 against every value in M10:AP10, then multiplies that 2-D array by the 1-D AR10:AR47 range. Functionally tricky, but conceptually much easier.

For both formulas, I think you should be able to see how to change the ranges in the formulas to match your sheet if your ranges change.

To research how they work, try Googling "excel array formula sumifs" for mine, and SUMPRODUCT for jtakw's. You'll find several good references.


Glad we could help, and let us know if you have followup questions.

 

[jtakw]

That's an Excellent explanation by Eric W.

Just wanted to add that, whenever you have the need to use Array formulas or SUMPRODUCT, to Not use Entire Column references (i.e. A:A, B:B, A:AC, etc.), it will slow down calculations dramatically.