Add up Q

S

Sean

I have a Database listing that shows Sales of Products by location by
Date. I want to extract from this a total for each item by location by
Date

My parameters are in the following cells

A1 = Location number
B1 = Product Code
C2 = Sales Date

My database has all 3 above detailed in 3 columns + a total Sales
Units column (I have all 4 named ranged, Locations, ProductCode,
SalesDate,SalesUnits)

I wish to return a value of total sales in A5, based on the detail I
have entered in A1, B1 and C1. How would I do this?

Thanks
 
R

Roger Govier

Hi Sean

=SUMPRODUCT((Locations=$A$1)*(ProductCode=$B$1)*
(TEXT(DATE(SalesDate,"yymmdd")=text($C$1,"yymmdd"))*Salesunits)

If it were everything for the Month of the date entered in C2 that you
required change to
=SUMPRODUCT((Locations=$A$1)*(ProductCode=$B$1)*
(NONTH(SalesDate,"yymmdd")=MONTH($C$1))*Salesunits)
 
S

Sean

Hi Sean

=SUMPRODUCT((Locations=$A$1)*(ProductCode=$B$1)*
(TEXT(DATE(SalesDate,"yymmdd")=text($C$1,"yymmdd"))*Salesunits)

If it were everything for the Month of the date entered in C2 that you
required change to
=SUMPRODUCT((Locations=$A$1)*(ProductCode=$B$1)*
(NONTH(SalesDate,"yymmdd")=MONTH($C$1))*Salesunits)

--
Regards

Roger Govier











- Show quoted text -
Click to expand...

Thanks Roger, no each Location/Product has only one entry for each
date.

Slight problem on your first formula, it says it has too few arguments

Secondly what is the significance of putting the date format as
"yymmdd", my database extracts it was dd/mm/yy (and I think there is a
time appended too
 
D

driller

Sean,

Do the named range represent a full column....?
I wish to return a value of total sales in A5, based on the detail I
have entered in A1, B1 and C1. How would I do this?
Click to expand...

From one of the param.
which one : C1 or C2 = Sales Date
Secondly what is the significance of putting the date format as
"yymmdd", my database extracts it was dd/mm/yy (and I think there is a
time appended too
Click to expand...

then the named range SalesDate should have been explained first to be like
SalesDateAndTime....


regards,
driller
 
Top