I am attempting to sum a range (multiple rows and columns) based on multiple criteria (location, type, and if it contains value >=0) in another tab in workbook. I have attempted sumifs, sum(if()), and array formulas but have been unsuccessful.
=SUMIFS('TRUCK Utilization'!P$7:P$367,'TRUCK Utilization'!P$7:P$367,">=0",Table2[TYPE],$B4,Table2[LOCATION],AL$3)
I actually want it to sum ALL values in range P11:AT367 (not just those in row P). When I select/input the entire range I get #Value or 0 and it doesn't sum any...
I want to put in a formula that will sum month to day (running total for the month) availability of truck in the particular location, if its the right type of truck, and if it was available (shown by a value greater than 0).
A Truck Utilization tab tracks the daily usage seen below.
<tbody>
</tbody>
The Truck Summary tab shows the following
<tbody>
</tbody>
At the far bottom right, you will see "0"s under Midland. I am attempting to calculate by availability (i have the daily formula figured out under the Truck Summary tab by using a sumifs), by location, and by type of truck. I attempted to use the Sumifs formula i have and adding the location range criteria but it returns a "0". It should pull the information from the Truck Utilization tab, right? Anyone have any ideas?
This following formula doesn't quite work:
=SUMPRODUCT(('TRUCK Utilization'!$G$7:$G$367=$B4)*('TRUCK Utilization'!$J$7:$J$367=AL$3)*('TRUCK Utilization'!$P$7:$AT$367>0))
However, when I do sample testing, the number it calculates is not correct.
On the TRUCK Utilization tab, I would like it to pick up only those values that equal a 0, 1, or 2 (and only count it as 1 if it is has either one of those numbers... right now it is summing those values I believe). Just so you know, the drop-down list in the TRUCK Utilization tab has those three number options as well as other text options (t-out, etc.). I wonder if it is counting some of them as 0 since the equation is looking for figures >0.. I attempted to use ">=0" but an error message pops up....???
Am I still able to sum the many columns (J7:J367) given the two criteria?... I imagine I would have to embed a Countif inside the Sumproduct function in order to count them correct?
=SUMIFS('TRUCK Utilization'!P$7:P$367,'TRUCK Utilization'!P$7:P$367,">=0",Table2[TYPE],$B4,Table2[LOCATION],AL$3)
I actually want it to sum ALL values in range P11:AT367 (not just those in row P). When I select/input the entire range I get #Value or 0 and it doesn't sum any...
I want to put in a formula that will sum month to day (running total for the month) availability of truck in the particular location, if its the right type of truck, and if it was available (shown by a value greater than 0).
A Truck Utilization tab tracks the daily usage seen below.
| UNIT # | Old # | TYPE | DESCRIPTION | ENTITY | LOCATION | YEAR | MODEL | Moved? | From | Date | 7/1 | 7/2 | 7/3 | 7/4 | 7/5 | 7/6 | 7/7 | 7/8 | 7/9 | 7/10 | 7/11 | 7/12 | 7/13 | 7/14 | 7/15 | 7/16 | 7/17 | 7/18 | 7/19 | 7/20 | 7/21 | 7/22 | 7/23 | 7/24 | 7/25 | 7/26 | 7/27 | 7/28 | 7/29 | 7/30 | 7/31 | MTD7 |
| 1 | Vacuum | Midland | 2005 | Peterbilt | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
<tbody>
</tbody>
The Truck Summary tab shows the following
| Truck Types | on List | 7/1 | 7/2 | 7/3 | 7/4 | 7/5 | 7/6 | 7/7 | 7/8 | 7/9 | 7/10 | 7/11 | 7/12 | 7/13 | 7/14 | 7/15 | 7/16 | 7/17 | 7/18 | 7/19 | 7/20 | 7/21 | 7/22 | 7/23 | 7/24 | 7/25 | 7/26 | 7/27 | 7/28 | 7/29 | 7/30 | 7/31 | MTD7 | Midland | ||
| Vacuum | - | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 40 | 0 | ||
| Available | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 30 | 0 | |||
| OOS-Internal | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 10 | 0 |
<tbody>
</tbody>
At the far bottom right, you will see "0"s under Midland. I am attempting to calculate by availability (i have the daily formula figured out under the Truck Summary tab by using a sumifs), by location, and by type of truck. I attempted to use the Sumifs formula i have and adding the location range criteria but it returns a "0". It should pull the information from the Truck Utilization tab, right? Anyone have any ideas?
This following formula doesn't quite work:
=SUMPRODUCT(('TRUCK Utilization'!$G$7:$G$367=$B4)*('TRUCK Utilization'!$J$7:$J$367=AL$3)*('TRUCK Utilization'!$P$7:$AT$367>0))
However, when I do sample testing, the number it calculates is not correct.
On the TRUCK Utilization tab, I would like it to pick up only those values that equal a 0, 1, or 2 (and only count it as 1 if it is has either one of those numbers... right now it is summing those values I believe). Just so you know, the drop-down list in the TRUCK Utilization tab has those three number options as well as other text options (t-out, etc.). I wonder if it is counting some of them as 0 since the equation is looking for figures >0.. I attempted to use ">=0" but an error message pops up....???
Am I still able to sum the many columns (J7:J367) given the two criteria?... I imagine I would have to embed a Countif inside the Sumproduct function in order to count them correct?
