Need help with complex SUM function...

  • Thread starter Thread starter Matthew Webb
  • Start date Start date
M

Matthew Webb

Hello, I need to sum progressive sales in a cell and the same with
estimates.

On the front sheet you enter the week ending date. The progressive
estimate cell will then take that date the look on a data sheet and sum
from the fixed cell location until it reaches the row that contains the
date... here is a little mock example.

I guess I use a sum function but I can't seem to integrate a lookup
function to give a reference... Any ideas I had a good try and I think
it might not be possible with standard excel functions...

----Data Sheet----
Date Est Sales
01-Jan 100 112
08-Jan 150 147
15-Jan 225 115
22-Jan 250 260
29-Jan 300
05-Feb 300
12-Feb 280
19-Feb 270
26-Feb 240

----Front Sheet----

w/e 22-jan

Prog. Est 725
Prog. Sales 634
 
Hi Matthew,

=SUMPRODUCT((Data!A1:A100<=Sheet1!A1*(B1:B100)

for Est

=SUMPRODUCT((Data!A1:A100<=Sheet1!A1*(C1:C100)

for Sales

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Bob Phillips said:
Hi Matthew,

=SUMPRODUCT((Data!A1:A100<=Sheet1!A1*(B1:B100)

for Est

=SUMPRODUCT((Data!A1:A100<=Sheet1!A1*(C1:C100)

for Sales
Click to expand...

This doesn't work, it returns 0.0 and don't I need to add the values not
multiply?
 
=SUM(cell ref for 1st col:INDIRECT(ADDRESS(row#,HLOOKUP(cell ref for date,AD$1:BH$155,2)))

I may not understand your question, but this formula allows me to create a month to date sum using various dates during the month; ie it sums across always starting at a fixed cell but stopping at the col representing the day of the month you entered in a cell (hlookup cell). The formula starts with a fixed cell address that is the first day of the month, then uses INDIRECT, ADDRESS and HLOOKUP functions to create the cell reference for the second half of the sum function.
 
Matthew,

It may look as though it multiplies, but the first part evaluates to True or
False values, so it is these that are multiplied by the actual values,
giving a net summing result.

I made errors in the formula, try

SUMPRODUCT((Data!A1:A100<=Sheet1!A1)*(B1:B100))

SUMPRODUCT((Data!A1:A100<=Sheet1!A1)*(C1:C100))


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Bob Phillips said:
Matthew,

It may look as though it multiplies, but the first part evaluates to True or
False values, so it is these that are multiplied by the actual values,
giving a net summing result.

I made errors in the formula, try

SUMPRODUCT((Data!A1:A100<=Sheet1!A1)*(B1:B100))

SUMPRODUCT((Data!A1:A100<=Sheet1!A1)*(C1:C100))
Click to expand...

Thank you so much bob! I must say it looks easy when you know how... The
help files aren't that good at explaining what they can do. I can't find
where it would teach you that.
 
Formulas with SumIf would be more appropriate.

Bob Phillips said:
Matthew,

It may look as though it multiplies, but the first part evaluates to True or
False values, so it is these that are multiplied by the actual values,
giving a net summing result.

I made errors in the formula, try

SUMPRODUCT((Data!A1:A100<=Sheet1!A1)*(B1:B100))

SUMPRODUCT((Data!A1:A100<=Sheet1!A1)*(C1:C100))
Click to expand...
[...]
 
Back
Top