Finding Max total values between date. Conditions

C

collegeitdept

Board Regular
Joined
Nov 14, 2008
Messages
185
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.

WeekProductWheel basePlatformViews
11/17/2013D ProductExtendedPlatform 41494
11/17/2013D ProductExtendedPlatform 68955
11/17/2013E ProductRegularPlatform 3A2380
11/14/2013E ProductRegularPlatform 3B391
11/17/2013F ProductRegularPlatform 12703
11/17/2013F ProductRegularPlatform 3A2635
11/17/2013F ProductRegularPlatform 3B6033
11/17/2013G ProductRegularPlatform 12437
11/24/2013B ProductRegularPlatform 3B253
11/24/2013C ProductRegularPlatform 3B10
11/24/2013B ProductRegularPlatform 3A3407
12/1/2013B ProductRegularPlatform 17527
12/1/2013B ProductRegularPlatform 217910
12/1/2013B ProductRegularPlatform 3B8061
12/1/2013B ProductExtendedPlatform 429515
12/1/2013B ProductExtendedPlatform 62215
12/1/2013C ProductRegularPlatform 1425
12/1/2013C ProductRegularPlatform 231
12/1/2013C ProductRegularPlatform 3A664
12/1/2013C ProductRegularPlatform 3B1593
12/1/2013D ProductRegularPlatform 1205
12/1/2013D ProductRegularPlatform 2890
12/1/2013D ProductRegularPlatform 3A2437
12/1/2013D ProductRegularPlatform 3B8012
12/1/2013D ProductExtendedPlatform 48339
12/1/2013D ProductExtendedPlatform 6312
12/1/2013F ProductRegularPlatform 113
12/1/2013F ProductRegularPlatform 3A143
12/1/2013F ProductRegularPlatform 3B499
12/8/2013A ProductRegularPlatform 3A53
12/8/2013A ProductRegularPlatform 3B560
12/8/2013B ProductRegularPlatform 12503
12/8/2013B ProductRegularPlatform 26033
12/8/2013B ProductRegularPlatform 3A1729
12/8/2013B ProductRegularPlatform 3B8607
12/8/2013B ProductExtendedPlatform 468249
12/8/2013B ProductExtendedPlatform 61494
12/8/2013C ProductRegularPlatform 1344096
12/8/2013C ProductRegularPlatform 2339
12/8/2013C ProductRegularPlatform 3A524
12/8/2013C ProductRegularPlatform 3B2703
12/8/2013D ProductRegularPlatform 197085
12/8/2013D ProductRegularPlatform 25610
12/8/2013D ProductRegularPlatform 3A1842
12/8/2013D ProductRegularPlatform 3B10965
12/8/2013D ProductRegularPlatform 528
12/8/2013D ProductExtendedPlatform 479598
12/8/2013D ProductExtendedPlatform 63569
12/8/2013F ProductRegularPlatform 12380
12/8/2013F ProductRegularPlatform 3A240
12/8/2013F ProductRegularPlatform 3B1022
12/8/2013G ProductRegularPlatform 3A104
12/8/2013G ProductRegularPlatform 3B987
12/15/2013A ProductRegularPlatform 1131
12/15/2013A ProductRegularPlatform 3A765
12/15/2013A ProductRegularPlatform 3B2493
12/15/2013A ProductExtendedPlatform 415515
12/15/2013A ProductExtendedPlatform 6980
12/15/2013B ProductRegularPlatform 12635
12/15/2013B ProductRegularPlatform 23834
12/15/2013B ProductRegularPlatform 3A1646
12/15/2013B ProductRegularPlatform 3B6084
12/15/2013C ProductRegularPlatform 16717
12/15/2013C ProductRegularPlatform 2148
12/15/2013C ProductRegularPlatform 3A555
12/15/2013C ProductRegularPlatform 3B2140
12/15/2013D ProductRegularPlatform 141053
12/15/2013D ProductRegularPlatform 2499
12/15/2013D ProductRegularPlatform 3A2116
12/15/2013D ProductRegularPlatform 3B5442
12/15/2013D ProductRegularPlatform 515
12/15/2013D ProductExtendedPlatform 460682
12/15/2013D ProductExtendedPlatform 62943
12/15/2013E ProductRegularPlatform 3A48
12/15/2013E ProductRegularPlatform 3B144
12/15/2013F ProductRegularPlatform 1391
12/15/2013F ProductRegularPlatform 3A1246
12/15/2013F ProductRegularPlatform 3B1382
12/15/2013G ProductRegularPlatform 181
12/15/2013G ProductRegularPlatform 3A1171
12/15/2013G ProductRegularPlatform 3B3679
12/15/2013G ProductExtendedPlatform 416257
12/15/2013G ProductExtendedPlatform 62285
12/15/2013A ProductExtendedPlatform 428781
12/15/2013D ProductExtendedPlatform 411093
12/15/2013G ProductExtendedPlatform 432842
12/22/2013B ProductRegularPlatform 3B339
12/22/2013C ProductRegularPlatform 3B79598
12/22/2013B ProductRegularPlatform 3A2437
12/22/2013B ProductExtendedPlatform 415515
12/22/2013D ProductRegularPlatform 3A2380
12/22/2013D ProductRegularPlatform 3B2116
12/22/2013D ProductRegularPlatform 518831
12/22/2013G ProductRegularPlatform 3A10449
12/22/2013G ProductRegularPlatform 3B8012

<colgroup><col><col span="2"><col><col></colgroup><tbody>
</tbody>


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)
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Sort by date Sort by votes
=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
 
Upvote 0
VBA Geek said:
=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
Click to expand...


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,
 
Upvote 0
collegeitdept said:
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,
Click to expand...



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!
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
VBA Help - Split Data Worksheet with helper sheet into separate workbooks, preserve all formatting and structure and PW protect sheet
Replies
6
Views
149
John_w
J
Cubist
  • Solved
Expand each row n number of times.
2
Replies
16
Views
296
Cubist
Cubist
P
  • Solved
Rolling year-to-date
Replies
5
Views
324
AhoyNC
A
K
  • Question
Needing Macros made to copy data from two sheets to one and sorted
Replies
0
Views
60
khelp11
K
A
  • Solved
Expand the range value and assign it to a new array
Replies
2
Views
87
anilsharaf
A

Forum statistics

Threads
1,215,455
Messages
6,124,935
Members
449,195
Latest member
Stevenciu

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top