Lookup with sum 2

[BlueMonkey]

Hi all,

Is there any formula will help me with the following query? I'm aiming to punch in a date and have a "vlookup" type function return sales figures for that date, plus the next 6 days.

Thanks in advance.

B.M.

 

[SkipVought]

Hi,

Check out the SUMPRODUCT function...

=sumproduct((YourDateRange>=StartDate)*(YourDateRange<=StartDate+6)*(YourSalesAmountRange))

I'd put StartDate in a cell and reference the cell.

Skip,

_[red][/red]

 

[BlueMonkey]

Cool, thanks. I'll try that now.

B.M.

 

[AnotherHiggins]

BlueMonkey: do you want the numbers for those 7 days rolled up into a single figure, or do you want to type in a single day and have 7 different cells populate, each with a different day's sales data?

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[BlueMonkey]

Single figure. Think I could probably knock together 7 figures

B.M.

 

[AnotherHiggins]

OK. I just wanted to be sure.

Go with Skip's suggestion.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[BlueMonkey]

thanks


B.M.

 

[BlueMonkey]

skip, will your solution work with multiple columns? Is the date range just the date col, or date col + data?

B.M.

 

[SkipVought]

Please post an example of how your table is structured.

Please be CLEAR, CONCISE & COMPLETE.

Skip,

_[red][/red]

 

[BlueMonkey]

OK.

Ideally, I would like a function that will generate me a single total sales figure for the week commencing, based on a user-input cell

Data table looks like (lets assume the Date label is in cell A1 - I will input the date I need into cell Q1).

Date North East South West
1/1 15 8 4 5
2/1 13 9 4 4

If this isn't possible, I will generate a total figure and look that up as per your earlier post/

B.M.

 

[AnotherHiggins]

YourDateRange should just be the single column that includes dates. YourSalesAmountRange should be the single column that contains your sales data.

A few notes about using SumProduct:

You cannot use an entire column in Sumproduct (A:A won't work). Also, each range in the SumProduct must contain the same number of cells.

I.e.
=suproduct((A1:A[COLOR=white red]1[/color]00="Test1")*(B1:B[COLOR=white red]1[/color]00="Test2"))
[tab]NOT
=suproduct((A1:A[COLOR=white red]1[/color]100="Test1")*(B1:B[COLOR=red white]5[/color]00="Test2"))


[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[SkipVought]

YourSalesAmountRange CAN be in more than one column.

CAVEAT: the ROW RANGE must be the same as YourDateRange.
So if YourDateRange is
[tt]
A2:A100
[/tt]
YourSalesAmountRange could be something like...
[tt]
B2:E100
[/tt]
So the sumproduct function would sum the data in the ROWS corresponding the the Date Criteria.

Skip,

_[red][/red]

 

[BlueMonkey]

Guys that is genius. Thanks both for your help.

I thought I knew Excel quite well - just goes to show! The more I see, the better it gets!!!

Thanks again

B.M.