SUMIFS getting too big - is there a better way?

InstructorAmberA

New Member
Joined
Oct 15, 2018
Messages
7
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/columnAPAQAR
10 StateOwner(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-MNOPQ
row9Food Truck CountAR 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

MrExcel MVP
Joined
Aug 18, 2015
Messages
8,996
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

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
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:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>J</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;">0</td></tr></tbody></table><p style="width:6.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet355</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">J8</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">(<font color="Red">AP$10:AP$47=M10:CZ10</font>)*$AR$10:$AR$47</font>)+SUM(<font color="Blue">M10:CZ10</font>)</td></tr></tbody></table></td></tr></table><br />
 

InstructorAmberA

New Member
Joined
Oct 15, 2018
Messages
7
@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

MrExcel MVP
Joined
Aug 18, 2015
Messages
8,996
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. :)
 
Last edited:

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
That's an Excellent explanation by Eric W. (y)

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

Forum statistics

Threads
1,082,360
Messages
5,364,939
Members
400,815
Latest member
Joaquin Phoenix

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top