Difficult Riddle: Summing values across various columns and rows

[drangel]

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.

[TABLE="class: cms_table, width: 2752"]
<tbody>[TR]
[TD="class: cms_table_xl190, width: 64"]UNIT #[/TD]
[TD="class: cms_table_xl196, width: 64"]Old #[/TD]
[TD="class: cms_table_xl191, width: 64"]TYPE[/TD]
[TD="class: cms_table_xl191, width: 64"]DESCRIPTION[/TD]
[TD="class: cms_table_xl190, width: 64"]ENTITY[/TD]
[TD="class: cms_table_xl190, width: 64"]LOCATION[/TD]
[TD="class: cms_table_xl192, width: 64"]YEAR[/TD]
[TD="class: cms_table_xl193, width: 64"]MODEL[/TD]
[TD="class: cms_table_xl197, width: 64"]Moved?[/TD]
[TD="class: cms_table_xl194, width: 64"]From[/TD]
[TD="class: cms_table_xl195, width: 64"]Date[/TD]
[TD="class: cms_table_xl198, width: 64"]7/1[/TD]
[TD="class: cms_table_xl198, width: 64"]7/2[/TD]
[TD="class: cms_table_xl198, width: 64"]7/3[/TD]
[TD="class: cms_table_xl198, width: 64"]7/4[/TD]
[TD="class: cms_table_xl198, width: 64"]7/5[/TD]
[TD="class: cms_table_xl198, width: 64"]7/6[/TD]
[TD="class: cms_table_xl198, width: 64"]7/7[/TD]
[TD="class: cms_table_xl198, width: 64"]7/8[/TD]
[TD="class: cms_table_xl198, width: 64"]7/9[/TD]
[TD="class: cms_table_xl198, width: 64"]7/10[/TD]
[TD="class: cms_table_xl198, width: 64"]7/11[/TD]
[TD="class: cms_table_xl198, width: 64"]7/12[/TD]
[TD="class: cms_table_xl198, width: 64"]7/13[/TD]
[TD="class: cms_table_xl198, width: 64"]7/14[/TD]
[TD="class: cms_table_xl198, width: 64"]7/15[/TD]
[TD="class: cms_table_xl198, width: 64"]7/16[/TD]
[TD="class: cms_table_xl198, width: 64"]7/17[/TD]
[TD="class: cms_table_xl198, width: 64"]7/18[/TD]
[TD="class: cms_table_xl198, width: 64"]7/19[/TD]
[TD="class: cms_table_xl198, width: 64"]7/20[/TD]
[TD="class: cms_table_xl198, width: 64"]7/21[/TD]
[TD="class: cms_table_xl198, width: 64"]7/22[/TD]
[TD="class: cms_table_xl198, width: 64"]7/23[/TD]
[TD="class: cms_table_xl198, width: 64"]7/24[/TD]
[TD="class: cms_table_xl198, width: 64"]7/25[/TD]
[TD="class: cms_table_xl198, width: 64"]7/26[/TD]
[TD="class: cms_table_xl198, width: 64"]7/27[/TD]
[TD="class: cms_table_xl198, width: 64"]7/28[/TD]
[TD="class: cms_table_xl198, width: 64"]7/29[/TD]
[TD="class: cms_table_xl198, width: 64"]7/30[/TD]
[TD="class: cms_table_xl198, width: 64"]7/31[/TD]
[TD="class: cms_table_xl198, width: 64"]MTD7[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl189"]1[/TD]
[TD="class: cms_table_xl182"][/TD]
[TD="class: cms_table_xl186"]Vacuum[/TD]
[TD="class: cms_table_xl186"][/TD]
[TD="class: cms_table_xl182"][/TD]
[TD="class: cms_table_xl182"]Midland[/TD]
[TD="class: cms_table_xl182"]2005[/TD]
[TD="class: cms_table_xl187"]Peterbilt[/TD]
[TD="class: cms_table_xl184"][/TD]
[TD="class: cms_table_xl188"][/TD]
[TD="class: cms_table_xl185"][/TD]
[TD="class: cms_table_xl182"]1[/TD]
[TD="class: cms_table_xl182"]1[/TD]
[TD="class: cms_table_xl182"]1[/TD]
[TD="class: cms_table_xl182"]1[/TD]
[TD="class: cms_table_xl182"]1[/TD]
[TD="class: cms_table_xl182"]1[/TD]
[TD="class: cms_table_xl182"]1[/TD]
[TD="class: cms_table_xl182"]1[/TD]
[TD="class: cms_table_xl182"]1[/TD]
[TD="class: cms_table_xl182"]1[/TD]
[TD="class: cms_table_xl182"]1[/TD]
[TD="class: cms_table_xl182"]1[/TD]
[TD="class: cms_table_xl182"]1[/TD]
[TD="class: cms_table_xl182"]1[/TD]
[TD="class: cms_table_xl182"]1[/TD]
[TD="class: cms_table_xl182"]1[/TD]
[TD="class: cms_table_xl182"]1[/TD]
[TD="class: cms_table_xl182"]1[/TD]
[TD="class: cms_table_xl182"]1[/TD]
[TD="class: cms_table_xl182"]1[/TD]
[TD="class: cms_table_xl182"]1[/TD]
[TD="class: cms_table_xl182"]1[/TD]
[TD="class: cms_table_xl182"]1[/TD]
[TD="class: cms_table_xl182"]1[/TD]
[TD="class: cms_table_xl182"]1[/TD]
[TD="class: cms_table_xl182"]1[/TD]
[TD="class: cms_table_xl182"]1[/TD]
[TD="class: cms_table_xl182"]1[/TD]
[TD="class: cms_table_xl182"]1[/TD]
[TD="class: cms_table_xl182"]1[/TD]
[TD="class: cms_table_xl182"]1[/TD]
[TD="class: cms_table_xl183"][/TD]
[/TR]
</tbody>[/TABLE]


The Truck Summary tab shows the following
[TABLE="class: cms_table, width: 2426"]
<tbody>[TR]
[TD]Truck Types[/TD]
[TD]on List[/TD]
[TD]7/1[/TD]
[TD]7/2[/TD]
[TD]7/3[/TD]
[TD]7/4[/TD]
[TD]7/5[/TD]
[TD]7/6[/TD]
[TD]7/7[/TD]
[TD]7/8[/TD]
[TD]7/9[/TD]
[TD]7/10[/TD]
[TD]7/11[/TD]
[TD]7/12[/TD]
[TD]7/13[/TD]
[TD]7/14[/TD]
[TD]7/15[/TD]
[TD]7/16[/TD]
[TD]7/17[/TD]
[TD]7/18[/TD]
[TD]7/19[/TD]
[TD]7/20[/TD]
[TD]7/21[/TD]
[TD]7/22[/TD]
[TD]7/23[/TD]
[TD]7/24[/TD]
[TD]7/25[/TD]
[TD]7/26[/TD]
[TD]7/27[/TD]
[TD]7/28[/TD]
[TD]7/29[/TD]
[TD]7/30[/TD]
[TD]7/31[/TD]
[TD]MTD7[/TD]
[TD][/TD]
[TD][/TD]
[TD]Midland[/TD]
[/TR]
[TR]
[TD]Vacuum[/TD]
[TD]-[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]40[/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Available[/TD]
[TD][/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]30[/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]OOS-Internal[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD]0


[/TD]
[/TR]
</tbody>[/TABLE]
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?

 

[Marcelo Branco]

Assuming TYPE in column G and LOCATION in column J, see if this does what you need

=SUMPRODUCT(('TRUCK Utilization'!$G$7:$G$367=$B4)*('TRUCK Utilization'!$J$7:$J$367=AL$3)*('TRUCK Utilization'!$P$7:$AT$367>0))

Hope this helps

M.

 

[drangel]

Assuming TYPE in column G and LOCATION in column J, see if this does what you need

=SUMPRODUCT(('TRUCK Utilization'!$G$7:$G$367=$B4)*('TRUCK Utilization'!$J$7:$J$367=AL$3)*('TRUCK Utilization'!$P$7:$AT$367>0))

Hope this helps

M.

Thank you Marcelo! However, the number it is calculating is not correct when I do sample testing.

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?

 

[Marcelo Branco]

Thank you Marcelo! However, the number it is calculating is not correct when I do sample testing.

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

See if this does what you need

=SUMPRODUCT(('TRUCK Utilization'!$G$7:$G$367=$B4)*('TRUCK Utilization'!$J$7:$J$367=AL$3)*ISNUMBER(MATCH('TRUCK Utilization'!$P$7:$AT$367,{0;1;2},0)))

M.

 

[drangel]

See if this does what you need

=SUMPRODUCT(('TRUCK Utilization'!$G$7:$G$367=$B4)*('TRUCK Utilization'!$J$7:$J$367=AL$3)*ISNUMBER(MATCH('TRUCK Utilization'!$P$7:$AT$367,{0;1;2},0)))

M.

Marcelo-

Unfortunately, the formula did not work. I don't understand why it wouldn't work. I also attempted to use OR(0,1,2) instead of {0;1;2}, enclose the ISNUMBER.. in a () but also didn't work/calculate anything. It returns a 0 as the result after inputting the different formulas... Any other ideas?

With regards to the first formula you presented:
=SUMPRODUCT(('TRUCK Utilization'!$E$7:$E$364=$B4)*('TRUCK Utilization'!$H$7:$H$364=AL$3)*('TRUCK Utilization'!$Q$7:$AU$364>0))... It think it was multiplying some of the numbers together.


I also tried
=SUMPRODUCT(('TRUCK Utilization'!$E$7:$E$364=$B4)*('TRUCK Utilization'!$H$7:$H$364=AL$3)*(COUNTIF('TRUCK Utilization'!$Q$7:$AU$364,">0")) and an OR(0,1,2) at the end but was unsuccessful....??

 

[Marcelo Branco]

Is hard to know why the formula is not working without seeing your data.

Please, test these formulas

=SUMPRODUCT(('TRUCK Utilization'!$G$7:$G$367=$B4)*('TRUCK Utilization'!$J$7:$J$367=AL$3)*('TRUCK Utilization'!$P$7:$AT$367=0))

=SUMPRODUCT(('TRUCK Utilization'!$G$7:$G$367=$B4)*('TRUCK Utilization'!$J$7:$J$367=AL$3)*('TRUCK Utilization'!$P$7:$AT$367=1))

=SUMPRODUCT(('TRUCK Utilization'!$G$7:$G$367=$B4)*('TRUCK Utilization'!$J$7:$J$367=AL$3)*('TRUCK Utilization'!$P$7:$AT$367=2))

M.