Add up Q

[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

 

[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)

 

[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 -

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

 

[Guest]

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?

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

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


regards,
driller