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:
[TABLE="width: 310"]
<colgroup><col><col span="3"><col></colgroup><tbody>[TR]
[TD="colspan: 2, align: left"]Row/column[/TD]
[TD="align: left"]AP[/TD]
[TD="align: left"]AQ[/TD]
[TD="align: left"]AR[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10 [/TD]
[TD][/TD]
[TD]State[/TD]
[TD="align: left"]Owner(s)[/TD]
[TD]Total Trucks for State[/TD]
[/TR]
[TR]
[TD="align: right"]11 [/TD]
[TD][/TD]
[TD]AR [/TD]
[TD]1 [/TD]
[TD]3 [/TD]
[/TR]
[TR]
[TD="align: right"]12 [/TD]
[TD][/TD]
[TD]CA [/TD]
[TD]5 [/TD]
[TD]18 [/TD]
[/TR]
[TR]
[TD="align: right"]13 [/TD]
[TD][/TD]
[TD]IA [/TD]
[TD]1 [/TD]
[TD]2 [/TD]
[/TR]
[TR]
[TD="align: right"]14 [/TD]
[TD][/TD]
[TD]IN [/TD]
[TD]1 [/TD]
[TD]6 [/TD]
[/TR]
[TR]
[TD="align: right"]15 [/TD]
[TD][/TD]
[TD]KY [/TD]
[TD]1 [/TD]
[TD]5 [/TD]
[/TR]
[TR]
[TD="align: right"]16 [/TD]
[TD][/TD]
[TD]MI [/TD]
[TD]1 [/TD]
[TD]1 [/TD]
[/TR]
</tbody>[/TABLE]
Example Tally Sheet below. Cell K9=FoodTruckCount Text. K10 is the location of the formula.
-L- is a blank column. AR is Cell M10
[TABLE="width: 462"]
<colgroup><col span="3"><col><col span="3"></colgroup><tbody>[TR]
[TD][/TD]
[TD]K -L-[/TD]
[TD="align: left"]M[/TD]
[TD="align: left"]N[/TD]
[TD="align: left"]O[/TD]
[TD="align: left"]P[/TD]
[TD="align: left"]Q[/TD]
[/TR]
[TR]
[TD]row9[/TD]
[TD]Food Truck Count[/TD]
[TD]AR [/TD]
[TD]CA [/TD]
[TD]IA [/TD]
[TD]IN [/TD]
[TD]KY [/TD]
[/TR]
[TR]
[TD="align: left"]row 10 [/TD]
[TD]8 [/TD]
[TD]AR [/TD]
[TD]2 [/TD]
[TD]IA [/TD]
[TD]1 [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: left"]row 11 [/TD]
[TD]0 [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: left"]row 12 [/TD]
[TD]43 [/TD]
[TD]AR [/TD]
[TD]CA [/TD]
[TD]IA [/TD]
[TD]IN [/TD]
[TD]KY [/TD]
[/TR]
</tbody>[/TABLE]
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!
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:
[TABLE="width: 310"]
<colgroup><col><col span="3"><col></colgroup><tbody>[TR]
[TD="colspan: 2, align: left"]Row/column[/TD]
[TD="align: left"]AP[/TD]
[TD="align: left"]AQ[/TD]
[TD="align: left"]AR[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10 [/TD]
[TD][/TD]
[TD]State[/TD]
[TD="align: left"]Owner(s)[/TD]
[TD]Total Trucks for State[/TD]
[/TR]
[TR]
[TD="align: right"]11 [/TD]
[TD][/TD]
[TD]AR [/TD]
[TD]1 [/TD]
[TD]3 [/TD]
[/TR]
[TR]
[TD="align: right"]12 [/TD]
[TD][/TD]
[TD]CA [/TD]
[TD]5 [/TD]
[TD]18 [/TD]
[/TR]
[TR]
[TD="align: right"]13 [/TD]
[TD][/TD]
[TD]IA [/TD]
[TD]1 [/TD]
[TD]2 [/TD]
[/TR]
[TR]
[TD="align: right"]14 [/TD]
[TD][/TD]
[TD]IN [/TD]
[TD]1 [/TD]
[TD]6 [/TD]
[/TR]
[TR]
[TD="align: right"]15 [/TD]
[TD][/TD]
[TD]KY [/TD]
[TD]1 [/TD]
[TD]5 [/TD]
[/TR]
[TR]
[TD="align: right"]16 [/TD]
[TD][/TD]
[TD]MI [/TD]
[TD]1 [/TD]
[TD]1 [/TD]
[/TR]
</tbody>[/TABLE]
Example Tally Sheet below. Cell K9=FoodTruckCount Text. K10 is the location of the formula.
-L- is a blank column. AR is Cell M10
[TABLE="width: 462"]
<colgroup><col span="3"><col><col span="3"></colgroup><tbody>[TR]
[TD][/TD]
[TD]K -L-[/TD]
[TD="align: left"]M[/TD]
[TD="align: left"]N[/TD]
[TD="align: left"]O[/TD]
[TD="align: left"]P[/TD]
[TD="align: left"]Q[/TD]
[/TR]
[TR]
[TD]row9[/TD]
[TD]Food Truck Count[/TD]
[TD]AR [/TD]
[TD]CA [/TD]
[TD]IA [/TD]
[TD]IN [/TD]
[TD]KY [/TD]
[/TR]
[TR]
[TD="align: left"]row 10 [/TD]
[TD]8 [/TD]
[TD]AR [/TD]
[TD]2 [/TD]
[TD]IA [/TD]
[TD]1 [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: left"]row 11 [/TD]
[TD]0 [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: left"]row 12 [/TD]
[TD]43 [/TD]
[TD]AR [/TD]
[TD]CA [/TD]
[TD]IA [/TD]
[TD]IN [/TD]
[TD]KY [/TD]
[/TR]
</tbody>[/TABLE]
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!