Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Add up Q
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Add up Q
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 "Sean" wrote in message oups.com... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Add up Q
On Mar 17, 12:44 pm, "Roger Govier"
wrote: 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 "Sean" wrote in message oups.com... 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- Hide quoted text - - 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Add up Q
Sean,
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) 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 -- ***** birds of the same feather flock together.. "Sean" wrote: On Mar 17, 12:44 pm, "Roger Govier" wrote: 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 "Sean" wrote in message oups.com... 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- Hide quoted text - - 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|