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

On Feb 7, 11:29*am, Ellie wrote:
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.