Finding Max total values between date. Conditions

[collegeitdept]

Hello,

I have a list of values in one column with the dates in column A, Product Name in column B, Wheelbase Category in column C, and Platform type in column D.

[TABLE="width: 353"]
<colgroup><col><col span="2"><col><col></colgroup><tbody>[TR]
[TD]Week[/TD]
[TD]Product[/TD]
[TD]Wheel base[/TD]
[TD]Platform[/TD]
[TD]Views[/TD]
[/TR]
[TR]
[TD="align: right"]11/17/2013[/TD]
[TD]D Product[/TD]
[TD]Extended[/TD]
[TD]Platform 4[/TD]
[TD="align: right"]1494[/TD]
[/TR]
[TR]
[TD="align: right"]11/17/2013[/TD]
[TD]D Product[/TD]
[TD]Extended[/TD]
[TD]Platform 6[/TD]
[TD="align: right"]8955[/TD]
[/TR]
[TR]
[TD="align: right"]11/17/2013[/TD]
[TD]E Product[/TD]
[TD]Regular[/TD]
[TD]Platform 3A[/TD]
[TD="align: right"]2380[/TD]
[/TR]
[TR]
[TD="align: right"]11/14/2013[/TD]
[TD]E Product[/TD]
[TD]Regular[/TD]
[TD]Platform 3B[/TD]
[TD="align: right"]391[/TD]
[/TR]
[TR]
[TD="align: right"]11/17/2013[/TD]
[TD]F Product[/TD]
[TD]Regular[/TD]
[TD]Platform 1[/TD]
[TD="align: right"]2703[/TD]
[/TR]
[TR]
[TD="align: right"]11/17/2013[/TD]
[TD]F Product[/TD]
[TD]Regular[/TD]
[TD]Platform 3A[/TD]
[TD="align: right"]2635[/TD]
[/TR]
[TR]
[TD="align: right"]11/17/2013[/TD]
[TD]F Product[/TD]
[TD]Regular[/TD]
[TD]Platform 3B[/TD]
[TD="align: right"]6033[/TD]
[/TR]
[TR]
[TD="align: right"]11/17/2013[/TD]
[TD]G Product[/TD]
[TD]Regular[/TD]
[TD]Platform 1[/TD]
[TD="align: right"]2437[/TD]
[/TR]
[TR]
[TD="align: right"]11/24/2013[/TD]
[TD]B Product[/TD]
[TD]Regular[/TD]
[TD]Platform 3B[/TD]
[TD="align: right"]253[/TD]
[/TR]
[TR]
[TD="align: right"]11/24/2013[/TD]
[TD]C Product[/TD]
[TD]Regular[/TD]
[TD]Platform 3B[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]11/24/2013[/TD]
[TD]B Product[/TD]
[TD]Regular[/TD]
[TD]Platform 3A[/TD]
[TD="align: right"]3407[/TD]
[/TR]
[TR]
[TD="align: right"]12/1/2013[/TD]
[TD]B Product[/TD]
[TD]Regular[/TD]
[TD]Platform 1[/TD]
[TD="align: right"]7527[/TD]
[/TR]
[TR]
[TD="align: right"]12/1/2013[/TD]
[TD]B Product[/TD]
[TD]Regular[/TD]
[TD]Platform 2[/TD]
[TD="align: right"]17910[/TD]
[/TR]
[TR]
[TD="align: right"]12/1/2013[/TD]
[TD]B Product[/TD]
[TD]Regular[/TD]
[TD]Platform 3B[/TD]
[TD="align: right"]8061[/TD]
[/TR]
[TR]
[TD="align: right"]12/1/2013[/TD]
[TD]B Product[/TD]
[TD]Extended[/TD]
[TD]Platform 4[/TD]
[TD="align: right"]29515[/TD]
[/TR]
[TR]
[TD="align: right"]12/1/2013[/TD]
[TD]B Product[/TD]
[TD]Extended[/TD]
[TD]Platform 6[/TD]
[TD="align: right"]2215[/TD]
[/TR]
[TR]
[TD="align: right"]12/1/2013[/TD]
[TD]C Product[/TD]
[TD]Regular[/TD]
[TD]Platform 1[/TD]
[TD="align: right"]425[/TD]
[/TR]
[TR]
[TD="align: right"]12/1/2013[/TD]
[TD]C Product[/TD]
[TD]Regular[/TD]
[TD]Platform 2[/TD]
[TD="align: right"]31[/TD]
[/TR]
[TR]
[TD="align: right"]12/1/2013[/TD]
[TD]C Product[/TD]
[TD]Regular[/TD]
[TD]Platform 3A[/TD]
[TD="align: right"]664[/TD]
[/TR]
[TR]
[TD="align: right"]12/1/2013[/TD]
[TD]C Product[/TD]
[TD]Regular[/TD]
[TD]Platform 3B[/TD]
[TD="align: right"]1593[/TD]
[/TR]
[TR]
[TD="align: right"]12/1/2013[/TD]
[TD]D Product[/TD]
[TD]Regular[/TD]
[TD]Platform 1[/TD]
[TD="align: right"]205[/TD]
[/TR]
[TR]
[TD="align: right"]12/1/2013[/TD]
[TD]D Product[/TD]
[TD]Regular[/TD]
[TD]Platform 2[/TD]
[TD="align: right"]890[/TD]
[/TR]
[TR]
[TD="align: right"]12/1/2013[/TD]
[TD]D Product[/TD]
[TD]Regular[/TD]
[TD]Platform 3A[/TD]
[TD="align: right"]2437[/TD]
[/TR]
[TR]
[TD="align: right"]12/1/2013[/TD]
[TD]D Product[/TD]
[TD]Regular[/TD]
[TD]Platform 3B[/TD]
[TD="align: right"]8012[/TD]
[/TR]
[TR]
[TD="align: right"]12/1/2013[/TD]
[TD]D Product[/TD]
[TD]Extended[/TD]
[TD]Platform 4[/TD]
[TD="align: right"]8339[/TD]
[/TR]
[TR]
[TD="align: right"]12/1/2013[/TD]
[TD]D Product[/TD]
[TD]Extended[/TD]
[TD]Platform 6[/TD]
[TD="align: right"]312[/TD]
[/TR]
[TR]
[TD="align: right"]12/1/2013[/TD]
[TD]F Product[/TD]
[TD]Regular[/TD]
[TD]Platform 1[/TD]
[TD="align: right"]13[/TD]
[/TR]
[TR]
[TD="align: right"]12/1/2013[/TD]
[TD]F Product[/TD]
[TD]Regular[/TD]
[TD]Platform 3A[/TD]
[TD="align: right"]143[/TD]
[/TR]
[TR]
[TD="align: right"]12/1/2013[/TD]
[TD]F Product[/TD]
[TD]Regular[/TD]
[TD]Platform 3B[/TD]
[TD="align: right"]499[/TD]
[/TR]
[TR]
[TD="align: right"]12/8/2013[/TD]
[TD]A Product[/TD]
[TD]Regular[/TD]
[TD]Platform 3A[/TD]
[TD="align: right"]53[/TD]
[/TR]
[TR]
[TD="align: right"]12/8/2013[/TD]
[TD]A Product[/TD]
[TD]Regular[/TD]
[TD]Platform 3B[/TD]
[TD="align: right"]560[/TD]
[/TR]
[TR]
[TD="align: right"]12/8/2013[/TD]
[TD]B Product[/TD]
[TD]Regular[/TD]
[TD]Platform 1[/TD]
[TD="align: right"]2503[/TD]
[/TR]
[TR]
[TD="align: right"]12/8/2013[/TD]
[TD]B Product[/TD]
[TD]Regular[/TD]
[TD]Platform 2[/TD]
[TD="align: right"]6033[/TD]
[/TR]
[TR]
[TD="align: right"]12/8/2013[/TD]
[TD]B Product[/TD]
[TD]Regular[/TD]
[TD]Platform 3A[/TD]
[TD="align: right"]1729[/TD]
[/TR]
[TR]
[TD="align: right"]12/8/2013[/TD]
[TD]B Product[/TD]
[TD]Regular[/TD]
[TD]Platform 3B[/TD]
[TD="align: right"]8607[/TD]
[/TR]
[TR]
[TD="align: right"]12/8/2013[/TD]
[TD]B Product[/TD]
[TD]Extended[/TD]
[TD]Platform 4[/TD]
[TD="align: right"]68249[/TD]
[/TR]
[TR]
[TD="align: right"]12/8/2013[/TD]
[TD]B Product[/TD]
[TD]Extended[/TD]
[TD]Platform 6[/TD]
[TD="align: right"]1494[/TD]
[/TR]
[TR]
[TD="align: right"]12/8/2013[/TD]
[TD]C Product[/TD]
[TD]Regular[/TD]
[TD]Platform 1[/TD]
[TD="align: right"]344096[/TD]
[/TR]
[TR]
[TD="align: right"]12/8/2013[/TD]
[TD]C Product[/TD]
[TD]Regular[/TD]
[TD]Platform 2[/TD]
[TD="align: right"]339[/TD]
[/TR]
[TR]
[TD="align: right"]12/8/2013[/TD]
[TD]C Product[/TD]
[TD]Regular[/TD]
[TD]Platform 3A[/TD]
[TD="align: right"]524[/TD]
[/TR]
[TR]
[TD="align: right"]12/8/2013[/TD]
[TD]C Product[/TD]
[TD]Regular[/TD]
[TD]Platform 3B[/TD]
[TD="align: right"]2703[/TD]
[/TR]
[TR]
[TD="align: right"]12/8/2013[/TD]
[TD]D Product[/TD]
[TD]Regular[/TD]
[TD]Platform 1[/TD]
[TD="align: right"]97085[/TD]
[/TR]
[TR]
[TD="align: right"]12/8/2013[/TD]
[TD]D Product[/TD]
[TD]Regular[/TD]
[TD]Platform 2[/TD]
[TD="align: right"]5610[/TD]
[/TR]
[TR]
[TD="align: right"]12/8/2013[/TD]
[TD]D Product[/TD]
[TD]Regular[/TD]
[TD]Platform 3A[/TD]
[TD="align: right"]1842[/TD]
[/TR]
[TR]
[TD="align: right"]12/8/2013[/TD]
[TD]D Product[/TD]
[TD]Regular[/TD]
[TD]Platform 3B[/TD]
[TD="align: right"]10965[/TD]
[/TR]
[TR]
[TD="align: right"]12/8/2013[/TD]
[TD]D Product[/TD]
[TD]Regular[/TD]
[TD]Platform 5[/TD]
[TD="align: right"]28[/TD]
[/TR]
[TR]
[TD="align: right"]12/8/2013[/TD]
[TD]D Product[/TD]
[TD]Extended[/TD]
[TD]Platform 4[/TD]
[TD="align: right"]79598[/TD]
[/TR]
[TR]
[TD="align: right"]12/8/2013[/TD]
[TD]D Product[/TD]
[TD]Extended[/TD]
[TD]Platform 6[/TD]
[TD="align: right"]3569[/TD]
[/TR]
[TR]
[TD="align: right"]12/8/2013[/TD]
[TD]F Product[/TD]
[TD]Regular[/TD]
[TD]Platform 1[/TD]
[TD="align: right"]2380[/TD]
[/TR]
[TR]
[TD="align: right"]12/8/2013[/TD]
[TD]F Product[/TD]
[TD]Regular[/TD]
[TD]Platform 3A[/TD]
[TD="align: right"]240[/TD]
[/TR]
[TR]
[TD="align: right"]12/8/2013[/TD]
[TD]F Product[/TD]
[TD]Regular[/TD]
[TD]Platform 3B[/TD]
[TD="align: right"]1022[/TD]
[/TR]
[TR]
[TD="align: right"]12/8/2013[/TD]
[TD]G Product[/TD]
[TD]Regular[/TD]
[TD]Platform 3A[/TD]
[TD="align: right"]104[/TD]
[/TR]
[TR]
[TD="align: right"]12/8/2013[/TD]
[TD]G Product[/TD]
[TD]Regular[/TD]
[TD]Platform 3B[/TD]
[TD="align: right"]987[/TD]
[/TR]
[TR]
[TD="align: right"]12/15/2013[/TD]
[TD]A Product[/TD]
[TD]Regular[/TD]
[TD]Platform 1[/TD]
[TD="align: right"]131[/TD]
[/TR]
[TR]
[TD="align: right"]12/15/2013[/TD]
[TD]A Product[/TD]
[TD]Regular[/TD]
[TD]Platform 3A[/TD]
[TD="align: right"]765[/TD]
[/TR]
[TR]
[TD="align: right"]12/15/2013[/TD]
[TD]A Product[/TD]
[TD]Regular[/TD]
[TD]Platform 3B[/TD]
[TD="align: right"]2493[/TD]
[/TR]
[TR]
[TD="align: right"]12/15/2013[/TD]
[TD]A Product[/TD]
[TD]Extended[/TD]
[TD]Platform 4[/TD]
[TD="align: right"]15515[/TD]
[/TR]
[TR]
[TD="align: right"]12/15/2013[/TD]
[TD]A Product[/TD]
[TD]Extended[/TD]
[TD]Platform 6[/TD]
[TD="align: right"]980[/TD]
[/TR]
[TR]
[TD="align: right"]12/15/2013[/TD]
[TD]B Product[/TD]
[TD]Regular[/TD]
[TD]Platform 1[/TD]
[TD="align: right"]2635[/TD]
[/TR]
[TR]
[TD="align: right"]12/15/2013[/TD]
[TD]B Product[/TD]
[TD]Regular[/TD]
[TD]Platform 2[/TD]
[TD="align: right"]3834[/TD]
[/TR]
[TR]
[TD="align: right"]12/15/2013[/TD]
[TD]B Product[/TD]
[TD]Regular[/TD]
[TD]Platform 3A[/TD]
[TD="align: right"]1646[/TD]
[/TR]
[TR]
[TD="align: right"]12/15/2013[/TD]
[TD]B Product[/TD]
[TD]Regular[/TD]
[TD]Platform 3B[/TD]
[TD="align: right"]6084[/TD]
[/TR]
[TR]
[TD="align: right"]12/15/2013[/TD]
[TD]C Product[/TD]
[TD]Regular[/TD]
[TD]Platform 1[/TD]
[TD="align: right"]6717[/TD]
[/TR]
[TR]
[TD="align: right"]12/15/2013[/TD]
[TD]C Product[/TD]
[TD]Regular[/TD]
[TD]Platform 2[/TD]
[TD="align: right"]148[/TD]
[/TR]
[TR]
[TD="align: right"]12/15/2013[/TD]
[TD]C Product[/TD]
[TD]Regular[/TD]
[TD]Platform 3A[/TD]
[TD="align: right"]555[/TD]
[/TR]
[TR]
[TD="align: right"]12/15/2013[/TD]
[TD]C Product[/TD]
[TD]Regular[/TD]
[TD]Platform 3B[/TD]
[TD="align: right"]2140[/TD]
[/TR]
[TR]
[TD="align: right"]12/15/2013[/TD]
[TD]D Product[/TD]
[TD]Regular[/TD]
[TD]Platform 1[/TD]
[TD="align: right"]41053[/TD]
[/TR]
[TR]
[TD="align: right"]12/15/2013[/TD]
[TD]D Product[/TD]
[TD]Regular[/TD]
[TD]Platform 2[/TD]
[TD="align: right"]499[/TD]
[/TR]
[TR]
[TD="align: right"]12/15/2013[/TD]
[TD]D Product[/TD]
[TD]Regular[/TD]
[TD]Platform 3A[/TD]
[TD="align: right"]2116[/TD]
[/TR]
[TR]
[TD="align: right"]12/15/2013[/TD]
[TD]D Product[/TD]
[TD]Regular[/TD]
[TD]Platform 3B[/TD]
[TD="align: right"]5442[/TD]
[/TR]
[TR]
[TD="align: right"]12/15/2013[/TD]
[TD]D Product[/TD]
[TD]Regular[/TD]
[TD]Platform 5[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD="align: right"]12/15/2013[/TD]
[TD]D Product[/TD]
[TD]Extended[/TD]
[TD]Platform 4[/TD]
[TD="align: right"]60682[/TD]
[/TR]
[TR]
[TD="align: right"]12/15/2013[/TD]
[TD]D Product[/TD]
[TD]Extended[/TD]
[TD]Platform 6[/TD]
[TD="align: right"]2943[/TD]
[/TR]
[TR]
[TD="align: right"]12/15/2013[/TD]
[TD]E Product[/TD]
[TD]Regular[/TD]
[TD]Platform 3A[/TD]
[TD="align: right"]48[/TD]
[/TR]
[TR]
[TD="align: right"]12/15/2013[/TD]
[TD]E Product[/TD]
[TD]Regular[/TD]
[TD]Platform 3B[/TD]
[TD="align: right"]144[/TD]
[/TR]
[TR]
[TD="align: right"]12/15/2013[/TD]
[TD]F Product[/TD]
[TD]Regular[/TD]
[TD]Platform 1[/TD]
[TD="align: right"]391[/TD]
[/TR]
[TR]
[TD="align: right"]12/15/2013[/TD]
[TD]F Product[/TD]
[TD]Regular[/TD]
[TD]Platform 3A[/TD]
[TD="align: right"]1246[/TD]
[/TR]
[TR]
[TD="align: right"]12/15/2013[/TD]
[TD]F Product[/TD]
[TD]Regular[/TD]
[TD]Platform 3B[/TD]
[TD="align: right"]1382[/TD]
[/TR]
[TR]
[TD="align: right"]12/15/2013[/TD]
[TD]G Product[/TD]
[TD]Regular[/TD]
[TD]Platform 1[/TD]
[TD="align: right"]81[/TD]
[/TR]
[TR]
[TD="align: right"]12/15/2013[/TD]
[TD]G Product[/TD]
[TD]Regular[/TD]
[TD]Platform 3A[/TD]
[TD="align: right"]1171[/TD]
[/TR]
[TR]
[TD="align: right"]12/15/2013[/TD]
[TD]G Product[/TD]
[TD]Regular[/TD]
[TD]Platform 3B[/TD]
[TD="align: right"]3679[/TD]
[/TR]
[TR]
[TD="align: right"]12/15/2013[/TD]
[TD]G Product[/TD]
[TD]Extended[/TD]
[TD]Platform 4[/TD]
[TD="align: right"]16257[/TD]
[/TR]
[TR]
[TD="align: right"]12/15/2013[/TD]
[TD]G Product[/TD]
[TD]Extended[/TD]
[TD]Platform 6[/TD]
[TD="align: right"]2285[/TD]
[/TR]
[TR]
[TD="align: right"]12/15/2013[/TD]
[TD]A Product[/TD]
[TD]Extended[/TD]
[TD]Platform 4[/TD]
[TD="align: right"]28781[/TD]
[/TR]
[TR]
[TD="align: right"]12/15/2013[/TD]
[TD]D Product[/TD]
[TD]Extended[/TD]
[TD]Platform 4[/TD]
[TD="align: right"]11093[/TD]
[/TR]
[TR]
[TD="align: right"]12/15/2013[/TD]
[TD]G Product[/TD]
[TD]Extended[/TD]
[TD]Platform 4[/TD]
[TD="align: right"]32842[/TD]
[/TR]
[TR]
[TD="align: right"]12/22/2013[/TD]
[TD]B Product[/TD]
[TD]Regular[/TD]
[TD]Platform 3B[/TD]
[TD="align: right"]339[/TD]
[/TR]
[TR]
[TD="align: right"]12/22/2013[/TD]
[TD]C Product[/TD]
[TD]Regular[/TD]
[TD]Platform 3B[/TD]
[TD="align: right"]79598[/TD]
[/TR]
[TR]
[TD="align: right"]12/22/2013[/TD]
[TD]B Product[/TD]
[TD]Regular[/TD]
[TD]Platform 3A[/TD]
[TD="align: right"]2437[/TD]
[/TR]
[TR]
[TD="align: right"]12/22/2013[/TD]
[TD]B Product[/TD]
[TD]Extended[/TD]
[TD]Platform 4[/TD]
[TD="align: right"]15515[/TD]
[/TR]
[TR]
[TD="align: right"]12/22/2013[/TD]
[TD]D Product[/TD]
[TD]Regular[/TD]
[TD]Platform 3A[/TD]
[TD="align: right"]2380[/TD]
[/TR]
[TR]
[TD="align: right"]12/22/2013[/TD]
[TD]D Product[/TD]
[TD]Regular[/TD]
[TD]Platform 3B[/TD]
[TD="align: right"]2116[/TD]
[/TR]
[TR]
[TD="align: right"]12/22/2013[/TD]
[TD]D Product[/TD]
[TD]Regular[/TD]
[TD]Platform 5[/TD]
[TD="align: right"]18831[/TD]
[/TR]
[TR]
[TD="align: right"]12/22/2013[/TD]
[TD]G Product[/TD]
[TD]Regular[/TD]
[TD]Platform 3A[/TD]
[TD="align: right"]10449[/TD]
[/TR]
[TR]
[TD="align: right"]12/22/2013[/TD]
[TD]G Product[/TD]
[TD]Regular[/TD]
[TD]Platform 3B[/TD]
[TD="align: right"]8012[/TD]
[/TR]
</tbody>[/TABLE]


I would like to find a way to find the MAX total values between a date range (Sumif between date range).

More info:
To get the total value for a date I use this formula:

=sumproduct(--(A:A=DATE1),E:E)

I need something like that for the Max formula. IE: What is the MAX of the summed values by date - between dates?







Also the Max summed values by date - for by product between date range.... by Platform, by wheelbase, etc....

=sumproduct((A:A=DATE1)*(B:B=Product D),E:E)

 

[VBA Geek]

=MAX(SUMIF($A$2:$A$96,G1+(ROW(INDIRECT("1:" & ((G2-G1)+1)))-1),$E$2:$E$96))

your data down columns A:E

your two dates range are in G1 and G2, G1 lower date, G2 higher date

confirm with Control Shift Enter

 

[collegeitdept]

=MAX(SUMIF($A$2:$A$96,G1+(ROW(INDIRECT("1:" & ((G2-G1)+1)))-1),$E$2:$E$96))

your data down columns A:E

your two dates range are in G1 and G2, G1 lower date, G2 higher date

confirm with Control Shift Enter

Thanks! it worked.

How would I do the same with additional conditions?

Like MAX sum values between date range for Product A.... and/or Platform 1... etc?



Also, does this assume that for each date there are the same number of products (because the ROW in your formula)? Some dates only have 2 or 3 products... So the number of products per date varies.

Regards,

 

[collegeitdept]

Thanks! it worked.

How would I do the same with additional conditions?

Like MAX sum values between date range for Product A.... and/or Platform 1... etc?



Also, does this assume that for each date there are the same number of products (because the ROW in your formula)? Some dates only have 2 or 3 products... So the number of products per date varies.

Regards,

DEALBREAKER:

I just tried it in the reporting document I use at work. It doesn't work.

The SUMIF formula doesn't work when referencing external workbook documents (the RAW data file I have).


Do you know of another formula that will work for external files and also with additional conditions (like Product, Platform, wheelbase, etc... within a certain date range) please?

Thanks!