SUMPRODUCT help

[Ellie]

I hope someone can help my colleague with this one. He has a spreadsheet in
Excel 2003.

The main data sheet consists of a series of columns, example as follows:-
Col A Col B Col C Col D Col E
Date Site Cases Plts Tonnes
1/1/08 060 25 5 2000
1/1/08 061 50 10 3900
etc for approx 20 further sites
2/1/08 060 32 8 3000
2/1/08 061 45 9 3505
and same as for 1/1/08
This continues on one spreadsheet for everydate of the year, plus each of
the sites.

On a separate sheet within the spreadsheet my colleague is now being
required to create a summarised sheet based on a selection of specific dates,
i.e. the end-user may select the start date as being 1/1/08 and the end date
as being any other date of the week, month or year so could be anywhere
between 1 and 350+ days. The 2nd part of this is whether the figures to be
seen for a respective site between dates is in either cases, pallets (plts)
or tonnes.

We have tried using SUMPRODUCT to calculate the start, end dates and site
number, but fail when asking to select whether wanting cases, tonnes or
pallets (plts).

I would be most grateful if anyone could advise whether there might be a
more suitable formula to help assist my colleague with this.

If you need any further information, please don't hesitate to contact me.

 

[Pete_UK]

Why not just return all three values in separate cells, one for Cases,
one for Pallets and the third for Tonnes? The SP formula for each of
these will be similar - just looking at different columns.

Hope this helps.

Pete

 

[Ellie]

Hi Pete

Thanks for the reply. We have tried this, but unfortunately the sum of the
cases, pallets or tonnes covering the respective dates and site selected only
brings up #VALUE!, this was why my colleague wasn't sure if a SUMPRODUCT was
the right formula to use.

 

[Pete_UK]

The #VALUE error implies that there is probably something wrong with
your data - it might look like numbers or dates but is in fact text.
You or your colleague will need to check the data thoroughly.

Also, you can't use a full column reference with SP, so if you post
the formula you have used here, then we might be able to comment
further on it.

Pete

 

[Ellie]

Hi Pete

The #VALUE error came from my colleague having selected 3 columns
originally. Value totals are still 0. Here is a copy of the formula used:-

=SUMPRODUCT(--($A$2:$A$26=$I$17),--($A$2:$A$26=$J$17),--($B$2:$B$26=$H$19),--($C$2:$C$26=I18))

Just as a trial, columns H, I and J my colleague is using as a test area on
the same sheet for the manual entries. I17 = start date of choice, J17 = end
date of choice, H19 = site number of choice and I18, J18 and K18 = respective
columns for population of totals for cases, pallets and tonnes.

Many thanks for your help.

Ellie

 

[Pete_UK]

I would suggest the following, then:

=SUMPRODUCT(--($A$2:$A$26>=$I$17),--($A$2:$A$26<=$J$17),--($B$2:$B$26=
$H$19),­($C$2:$C$26))

this will give you the totals of column C where the cells meet the
criteria. Note that you want to include dates that are greater than or
equal to the start date, less than or equal to the end date.

Similar formulae:

=SUMPRODUCT(--($A$2:$A$26>=$I$17),--($A$2:$A$26<=$J$17),--($B$2:$B$26=
$H$19),­($D$2:$D$26))

will give you total of pallets (i.e. column D in formula)

=SUMPRODUCT(--($A$2:$A$26>=$I$17),--($A$2:$A$26<=$J$17),--($B$2:$B$26=
$H$19),­($E$2:$E$26))

will give you a total of the Tonnes column.

Put these in adjacent cells - presumably I18, J18 and K18.

Hope this helps.

Pete

 

[David Biddulph]

Unless I17 and J17 have identical values, I would expect an output of zero
from your formula, as otherwise you can't satisfy the first and second
conditions together.

 

[Ellie]

It does help and thank you so much for your help and time.

Ellie

 

[Pete_UK]

Glad to hear that, Ellie - thanks for feeding back.

Pete