#### InstructorAmberA

##### New Member

- Joined
- Oct 15, 2018

- Messages
- 7

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. )

(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!