Manual entry date range to lookup and sum according to dates entered

H

hampo692002

New Member
Joined
May 31, 2013
Messages
3
Hi

I was wondering if it would be possible to create a formula to lookup and sum a range from a manually entered date range, an example is below;

[TABLE="width: 472"]
<COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" width=82><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3620" width=99><COL style="WIDTH: 48pt" span=6 width=64><TBODY>[TR]
[TD="class: xl66, width: 64, bgcolor: yellow"]A[/TD]
[TD="class: xl66, width: 82, bgcolor: yellow"]B[/TD]
[TD="class: xl66, width: 99, bgcolor: yellow"]C[/TD]
[TD="class: xl66, width: 64, bgcolor: yellow"]D[/TD]
[TD="class: xl66, width: 64, bgcolor: yellow"]E[/TD]
[TD="class: xl66, width: 64, bgcolor: yellow"]F[/TD]
[TD="class: xl66, width: 64, bgcolor: yellow"]G[/TD]
[TD="class: xl66, width: 64, bgcolor: yellow"]H[/TD]
[TD="class: xl66, width: 64, bgcolor: yellow"]I[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: yellow, align: right"]1[/TD]
[TD="class: xl67, bgcolor: transparent"]From[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]01 June 2012[/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: yellow, align: right"]2[/TD]
[TD="class: xl67, bgcolor: transparent"]To[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]31 August 2013[/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: #b7dee8"]19[/TD]
[TD="class: xl70, bgcolor: #b7dee8"]22[/TD]
[TD="class: xl70, bgcolor: #b7dee8"]22[/TD]
[TD="class: xl70, bgcolor: #b7dee8"]20[/TD]
[TD="class: xl70, bgcolor: #b7dee8"]23[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: yellow, align: right"]3[/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl71, bgcolor: transparent"]Jun-12[/TD]
[TD="class: xl71, bgcolor: transparent"]Jul-12[/TD]
[TD="class: xl71, bgcolor: transparent"]Aug-12[/TD]
[TD="class: xl71, bgcolor: transparent"]Sep-12[/TD]
[TD="class: xl71, bgcolor: transparent"]Oct-12[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: yellow, align: right"]4[/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]12[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]8[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]6[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: yellow, align: right"]5[/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[/TR]
</TBODY>[/TABLE]



Is it possible to enter a start date in C1 and an end date in C2 which will look up from E4 to G4 choosing the cell references from the entered information in C1 & C2?


I have been pondering this for a while so any help would be greatly appreciated.

Thanks

Paul
 
Sort by date Sort by votes
BenMiller said:
I'm not sure I understand your question fully, but have a look at this: Sum of Values Between 2 Dates [Excel Formulas] | Chandoo.org - Learn Microsoft Excel Online
Click to expand...

The information as below, i need a lookup sum that will create a horizontal sum within the cell range E4:I4 for example and return a sum of the amount within this range defined by the start and end dates in C1 & C2.


[TABLE="width: 472"]
<COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" width=82><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3620" width=99><COL style="WIDTH: 48pt" span=6 width=64><TBODY>[TR]
[TD="class: xl68, width: 64, bgcolor: yellow"]A[/TD]
[TD="class: xl68, width: 82, bgcolor: yellow"]B[/TD]
[TD="class: xl68, width: 99, bgcolor: yellow"]C[/TD]
[TD="class: xl68, width: 64, bgcolor: yellow"]D[/TD]
[TD="class: xl68, width: 64, bgcolor: yellow"]E[/TD]
[TD="class: xl68, width: 64, bgcolor: yellow"]F[/TD]
[TD="class: xl68, width: 64, bgcolor: yellow"]G[/TD]
[TD="class: xl68, width: 64, bgcolor: yellow"]H[/TD]
[TD="class: xl68, width: 64, bgcolor: yellow"]I[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: yellow, align: right"]1[/TD]
[TD="class: xl69, bgcolor: transparent"]From[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]01 June 2012[/TD]
[TD="class: xl71, bgcolor: transparent"] [/TD]
[TD="class: xl71, bgcolor: transparent"] [/TD]
[TD="class: xl71, bgcolor: transparent"] [/TD]
[TD="class: xl71, bgcolor: transparent"] [/TD]
[TD="class: xl71, bgcolor: transparent"] [/TD]
[TD="class: xl71, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: yellow, align: right"]2[/TD]
[TD="class: xl69, bgcolor: transparent"]To[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]31 August 2013[/TD]
[TD="class: xl71, bgcolor: transparent"] [/TD]
[TD="class: xl72, bgcolor: #b7dee8"]19[/TD]
[TD="class: xl72, bgcolor: #b7dee8"]22[/TD]
[TD="class: xl72, bgcolor: #b7dee8"]22[/TD]
[TD="class: xl72, bgcolor: #b7dee8"]20[/TD]
[TD="class: xl72, bgcolor: #b7dee8"]23[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: yellow, align: right"]3[/TD]
[TD="class: xl71, bgcolor: transparent"] [/TD]
[TD="class: xl74, bgcolor: transparent"] [/TD]
[TD="class: xl71, bgcolor: transparent"] [/TD]
[TD="class: xl73, bgcolor: transparent"]Jun-12[/TD]
[TD="class: xl73, bgcolor: transparent"]Jul-12[/TD]
[TD="class: xl73, bgcolor: transparent"]Aug-12[/TD]
[TD="class: xl73, bgcolor: transparent"]Sep-12[/TD]
[TD="class: xl73, bgcolor: transparent"]Oct-12[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: yellow, align: right"]4[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl76, bgcolor: transparent"] formula here[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]12[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]8[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]6[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: yellow, align: right"]5[/TD]
[TD="class: xl71, bgcolor: transparent"] [/TD]
[TD="class: xl75, bgcolor: transparent"] [/TD]
[TD="class: xl71, bgcolor: transparent"] [/TD]
[TD="class: xl71, bgcolor: transparent"] [/TD]
[TD="class: xl71, bgcolor: transparent"] [/TD]
[TD="class: xl71, bgcolor: transparent"] [/TD]
[TD="class: xl71, bgcolor: transparent"] [/TD]
[TD="class: xl71, bgcolor: transparent"] [/TD]
[/TR]
</TBODY>[/TABLE]


For example as above start date of June 2012 and end date august 2012 needs to report sum from cells E4 to G4. The formula will need to reference C1 to alocate the start of the sum range and C2 as the end of the sum range. Therefore as above the formula in C4 needs to report 30 in this case. If the end date was changed to Spet 2012 then C4 would need to report 36 and so on.

I hope this has been a little clearer on what I am looking to achieve?

Thanks for your assistance it is greatly appreciated:)
 
Upvote 0
Row 3 is month and year? And what is in row 2? Where exactly are your dates? Or do you just have months?
 
Upvote 0
Yes row 3 is month and year at the moment, obviously I would have to have row 3 labelled appropriately to the dates that will be manually entered in C1 & C2 (or I will have to reference them in another way) Row 2 is of no consequence to this as it is just the number of working days within the month below and forms a different part of the same spread sheet.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
1K
smozgur
smozgur
Lacan
  • Solved
How to lookup expense category and sum value accordingly date month?
Replies
5
Views
223
Lacan
Lacan
A
  • Solved
Lookup PAAF ID based on Worker ID and date range
Replies
3
Views
165
Fluff
Fluff
G
  • Question Question
Sum a range of a nested index match
Replies
3
Views
911
Peter_SSs
Peter_SSs
M
  • Question Question
Help with FilterXML formula
Replies
0
Views
2K
montex01
M

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