Variable condition/range summing in Excel 1

[Roonaldez]

Apologies if the title is not appropiate...

If I have two columns of data and wish to sum one column, as far as the criteria specified elsewhere is met in another column....for example....column of marketing spend in column A, number of sales in column B.
A user specifies a marketing spend of £500k (in any cell)...running from the top of Column A as far down the list as is necessary to total £500k (with a 3% variance) would determine the number of rows to sum from column B....so, if the values in column a total £500k between rows 1 and 12, then I would sum cells B1:B12...

I have tried sumif, sumproduct, and setting offset ranges but I can't see how to determine at which point to end the summing down column a, to use as a range determining tool for the summing of column b....

Any ideas?

thank you for your time.

 

[SkipVought]

Please post a respresentative sample of data AND the result that you expect to get.

Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]

 

[Roonaldez]

Spend Sales
£245.00 1
£210.00 7
£189.00 12
£187.00 5
£184.00 2
£168.00 5
£156.00 6
£145.00 4
£145.00 4
£120.00 2

Chosen value of Marketing spend is £644

So…A user wants to spend £650…the first three rows total £644, which is within an acceptable range of the chosen spend….so what would the total sales
be for this spend? Sum column B from B1:B4 - 20 sales.

If the user had entered a spend of £1010, then the spend would total from A2 to A6, therefore we would total B2 to B6.


A user could specify any spend, and we need to total the values in column A to sum to that value, which determines the range to sum in column B

 

[SkipVought]

Make a formual in column C to return the CumSpend amount.

Name the Data Ranges with the column headings.

Name the Spend Limit, SpendLimit

In an empty cell...
[tt]
=SUMPRODUCT((CumSpend<=SpendLimit)*(Sales))
[/tt]



Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]