How Do I DO IF with DATE
in [Functions]
|
From: Dig on 3 Jul 2008 12:08 I have a workbook with 2 sheets. The first sheet has a report for individuals sales. Each row has a date then cust name then amount from left to right. The other sheet has a table setup to do monthly totals to figure commissions. First is the rep name then total sales for a given month then commission percent then commission amount from Left to Right. What I want to do is have the TOTAL SALES in SHEET 2 look in SHEET 1 and TOTAL all sales for say any date that has a 7 as the first number (this would be for JULY). Obviously I have other cells that will need to look and see if there is an 8 (AUGUST) as the first date number and so on but once I figure the first on out it is a simple matter of repeating the process. Can I use an IF statement or is there a better way to do this. You help is appreciated greatly Thanks
From: Gary Brown on 3 Jul 2008 12:50 I think you may need a cheater column with the formula '=Month(date)' and the SumProduct function. -- Hope this helps. If this post was helpfull, please remember to click on the ''''YES'''' button at the bottom of the screen. Thanks, Gary Brown "Dig" wrote: > I have a workbook with 2 sheets. The first sheet has a report for individuals > sales. Each row has a date then cust name then amount from left to right. > > The other sheet has a table setup to do monthly totals to figure > commissions. First is the rep name then total sales for a given month then > commission percent then commission amount from Left to Right. > > What I want to do is have the TOTAL SALES in SHEET 2 look in SHEET 1 and > TOTAL all sales for say any date that has a 7 as the first number (this would > be for JULY). Obviously I have other cells that will need to look and see if > there is an 8 (AUGUST) as the first date number and so on but once I figure > the first on out it is a simple matter of repeating the process. > > Can I use an IF statement or is there a better way to do this. > > You help is appreciated greatly > Thanks
From: Sandy Mann on 3 Jul 2008 13:48 You don't mention the Rep's name in Sheet 1 so assuming that in Sheet 1: Column A: Rep's name Column B: Date Column C: Customer Name Column D: Amount In Sheet 2: Column A: Rep's name Column B: =SUMPRODUCT((Sheet1!A2:A200=A2)*(MONTH(Sheet1!B2:B200)=7)*Sheet1!C2:C200) (ie A2 in Sheet2 is the Rep's name). Adjust the ranges to your requirements. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2(a)mailinator.com Replace @mailinator.com with @tiscali.co.uk "Dig" <Dig(a)discussions.microsoft.com> wrote in message news:E0DA8E80-7991-4C5C-A0E9-3D4905A8051E(a)microsoft.com... >I have a workbook with 2 sheets. The first sheet has a report for >individuals > sales. Each row has a date then cust name then amount from left to right. > > The other sheet has a table setup to do monthly totals to figure > commissions. First is the rep name then total sales for a given month then > commission percent then commission amount from Left to Right. > > What I want to do is have the TOTAL SALES in SHEET 2 look in SHEET 1 and > TOTAL all sales for say any date that has a 7 as the first number (this > would > be for JULY). Obviously I have other cells that will need to look and see > if > there is an 8 (AUGUST) as the first date number and so on but once I > figure > the first on out it is a simple matter of repeating the process. > > Can I use an IF statement or is there a better way to do this. > > You help is appreciated greatly > Thanks >
|
Pages: 1 Prev: split data into seperate worksheets by criteria Next: Change Workbook Password |