If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Date Format and Sumproduct
I have a column A for the date MM/DD/YYYY format.
I need to have a sumproduct formula look thru the database and return sales totals for salesperson by month, day and year separately. For example: Column A is date B is salesperson C is sales On a separate page in the workbook, I have a report with these selection criteria... A1 is salesperson A2 is month A3 is year I need a formula to return sales for this salesperson for the month and for the year. Looking ahead, the report/database will span several years. Also, is there a way to sumproduct sales by day of the week such as Tuesdays using the mm/dd/yyyy format? I seem to struggle with dates and sumproduct. Any help would be greatly appreciated. |
#2
|
|||
|
|||
Date Format and Sumproduct
On a separate page in the workbook, I have
a report with these selection criteria... A1 is salesperson A2 is month A3 is year In A2, how are you entering the month, as the month number or the month name? A2 = 5 (month 5 = May) A2 = May -- Biff Microsoft Excel MVP "wx4usa" wrote in message ... I have a column A for the date MM/DD/YYYY format. I need to have a sumproduct formula look thru the database and return sales totals for salesperson by month, day and year separately. For example: Column A is date B is salesperson C is sales On a separate page in the workbook, I have a report with these selection criteria... A1 is salesperson A2 is month A3 is year I need a formula to return sales for this salesperson for the month and for the year. Looking ahead, the report/database will span several years. Also, is there a way to sumproduct sales by day of the week such as Tuesdays using the mm/dd/yyyy format? I seem to struggle with dates and sumproduct. Any help would be greatly appreciated. |
#3
|
|||
|
|||
Date Format and Sumproduct
On Dec 28, 4:46*pm, "T. Valko" wrote:
On a separate page in the workbook, I have a report with these selection criteria... A1 is salesperson A2 is month A3 is year In A2, how are you entering the month, as the month number or the month name? A2 = 5 (month 5 = May) A2 = May -- Biff Microsoft Excel MVP "wx4usa" wrote in message ... I have a column A for the date MM/DD/YYYY format. I need to have a sumproduct formula look thru the database and return sales totals for salesperson by month, day and year separately. For example: Column A is date B is salesperson C is sales On a separate page in the workbook, I have a report with these selection criteria... A1 is salesperson A2 is month A3 is year I need a formula to return sales for this salesperson for the month and for the year. *Looking ahead, the report/database will span several years. Also, is there a way to sumproduct sales by day of the week such as Tuesdays using the mm/dd/yyyy format? I seem to struggle with dates and sumproduct. Any help would be greatly appreciated. I am entering Month Name such as May. |
#4
|
|||
|
|||
Date Format and Sumproduct
Try these...
Sum by salesperson for month and year: =SUMPRODUCT(--(Sheet1!B1:B20=A1),--(TEXT(Sheet1!A1:A20,"mmmyyyy")=A2&A3),Sheet1!C1:C2 0) Sum for a specific weekday such as Tuesday: =SUMPRODUCT(--(TEXT(Sheet1!A1:A20,"ddd")="Tue"),Sheet1!C1:C20) Or: =SUMPRODUCT(--(WEEKDAY(Sheet1!A1:A20)=n),Sheet1!C1:C20) Where n = the weekday number: 1 = Sun 2 = Mon 3 = Tue 4 = Wed 5 = Thu 6 = Fri 7 = Sat -- Biff Microsoft Excel MVP "wx4usa" wrote in message ... On Dec 28, 4:46 pm, "T. Valko" wrote: On a separate page in the workbook, I have a report with these selection criteria... A1 is salesperson A2 is month A3 is year In A2, how are you entering the month, as the month number or the month name? A2 = 5 (month 5 = May) A2 = May -- Biff Microsoft Excel MVP "wx4usa" wrote in message ... I have a column A for the date MM/DD/YYYY format. I need to have a sumproduct formula look thru the database and return sales totals for salesperson by month, day and year separately. For example: Column A is date B is salesperson C is sales On a separate page in the workbook, I have a report with these selection criteria... A1 is salesperson A2 is month A3 is year I need a formula to return sales for this salesperson for the month and for the year. Looking ahead, the report/database will span several years. Also, is there a way to sumproduct sales by day of the week such as Tuesdays using the mm/dd/yyyy format? I seem to struggle with dates and sumproduct. Any help would be greatly appreciated. I am entering Month Name such as May. |
#5
|
|||
|
|||
Date Format and Sumproduct
On Dec 28, 5:45*pm, "T. Valko" wrote:
Try these... Sum by salesperson for month and year: =SUMPRODUCT(--(Sheet1!B1:B20=A1),--(TEXT(Sheet1!A1:A20,"mmmyyyy")=A2&A3),Sheet1!C1:C2 0) Sum for a specific weekday such as Tuesday: =SUMPRODUCT(--(TEXT(Sheet1!A1:A20,"ddd")="Tue"),Sheet1!C1:C20) Or: =SUMPRODUCT(--(WEEKDAY(Sheet1!A1:A20)=n),Sheet1!C1:C20) Where n = the weekday number: 1 = Sun 2 = Mon 3 = Tue 4 = Wed 5 = Thu 6 = Fri 7 = Sat -- Biff Microsoft Excel MVP "wx4usa" wrote in message ... On Dec 28, 4:46 pm, "T. Valko" wrote: On a separate page in the workbook, I have a report with these selection criteria... A1 is salesperson A2 is month A3 is year In A2, how are you entering the month, as the month number or the month name? A2 = 5 (month 5 = May) A2 = May -- Biff Microsoft Excel MVP "wx4usa" wrote in message ... I have a column A for the date MM/DD/YYYY format. I need to have a sumproduct formula look thru the database and return sales totals for salesperson by month, day and year separately. For example: Column A is date B is salesperson C is sales On a separate page in the workbook, I have a report with these selection criteria... A1 is salesperson A2 is month A3 is year I need a formula to return sales for this salesperson for the month and for the year. Looking ahead, the report/database will span several years. Also, is there a way to sumproduct sales by day of the week such as Tuesdays using the mm/dd/yyyy format? I seem to struggle with dates and sumproduct. Any help would be greatly appreciated. I am entering Month Name such as May. Hi Biff How can I also do a specific day such as Dec 1, 2009? 12/01/2009? I tried this and it doesnt work..... =SUMPRODUCT(--($B$5:$B$21=$A$1),--(TEXT($A$5:$A$21,"mmmmddyyy")=$A$2&$A $3&A4),$C$5:$C$21) where December is in a2, 1 is on a3 and 2009 is in a4 |
#6
|
|||
|
|||
Date Format and Sumproduct
Although the dates are in European format and the formula is on the same
sheet - the principle remains the same. I'm sure you will manage. http://img707.imageshack.us/img707/2626/nonamey.png Micky "wx4usa" wrote: I have a column A for the date MM/DD/YYYY format. I need to have a sumproduct formula look thru the database and return sales totals for salesperson by month, day and year separately. For example: Column A is date B is salesperson C is sales On a separate page in the workbook, I have a report with these selection criteria... A1 is salesperson A2 is month A3 is year I need a formula to return sales for this salesperson for the month and for the year. Looking ahead, the report/database will span several years. Also, is there a way to sumproduct sales by day of the week such as Tuesdays using the mm/dd/yyyy format? I seem to struggle with dates and sumproduct. Any help would be greatly appreciated. . |
#7
|
|||
|
|||
Date Format and Sumproduct
I hope this helps although shown on the same sheet and the dates are in the
European format. http://img163.imageshack.us/img163/4766/nonameam.png The principal for summing as per weekdays is the same. Use the WEEKDAY Function in order to check the day of the week [1-7] Micky "wx4usa" wrote: I have a column A for the date MM/DD/YYYY format. I need to have a sumproduct formula look thru the database and return sales totals for salesperson by month, day and year separately. For example: Column A is date B is salesperson C is sales On a separate page in the workbook, I have a report with these selection criteria... A1 is salesperson A2 is month A3 is year I need a formula to return sales for this salesperson for the month and for the year. Looking ahead, the report/database will span several years. Also, is there a way to sumproduct sales by day of the week such as Tuesdays using the mm/dd/yyyy format? I seem to struggle with dates and sumproduct. Any help would be greatly appreciated. . |
#8
|
|||
|
|||
Date Format and Sumproduct
It would be a lot easier if you were to just enter the whole data (as a
date) in one cell: A2 = 12/1/2009 (or 1/12/2009 depending on your local date format) =SUMPRODUCT(--($B$5:$B$21=$A$1),--($A$5:$A$21=$A$2),$C$5:$C$21) -- Biff Microsoft Excel MVP "wx4usa" wrote in message ... On Dec 28, 5:45 pm, "T. Valko" wrote: Try these... Sum by salesperson for month and year: =SUMPRODUCT(--(Sheet1!B1:B20=A1),--(TEXT(Sheet1!A1:A20,"mmmyyyy")=A2&A3),Sheet1!C1:C2 0) Sum for a specific weekday such as Tuesday: =SUMPRODUCT(--(TEXT(Sheet1!A1:A20,"ddd")="Tue"),Sheet1!C1:C20) Or: =SUMPRODUCT(--(WEEKDAY(Sheet1!A1:A20)=n),Sheet1!C1:C20) Where n = the weekday number: 1 = Sun 2 = Mon 3 = Tue 4 = Wed 5 = Thu 6 = Fri 7 = Sat -- Biff Microsoft Excel MVP "wx4usa" wrote in message ... On Dec 28, 4:46 pm, "T. Valko" wrote: On a separate page in the workbook, I have a report with these selection criteria... A1 is salesperson A2 is month A3 is year In A2, how are you entering the month, as the month number or the month name? A2 = 5 (month 5 = May) A2 = May -- Biff Microsoft Excel MVP "wx4usa" wrote in message ... I have a column A for the date MM/DD/YYYY format. I need to have a sumproduct formula look thru the database and return sales totals for salesperson by month, day and year separately. For example: Column A is date B is salesperson C is sales On a separate page in the workbook, I have a report with these selection criteria... A1 is salesperson A2 is month A3 is year I need a formula to return sales for this salesperson for the month and for the year. Looking ahead, the report/database will span several years. Also, is there a way to sumproduct sales by day of the week such as Tuesdays using the mm/dd/yyyy format? I seem to struggle with dates and sumproduct. Any help would be greatly appreciated. I am entering Month Name such as May. Hi Biff How can I also do a specific day such as Dec 1, 2009? 12/01/2009? I tried this and it doesnt work..... =SUMPRODUCT(--($B$5:$B$21=$A$1),--(TEXT($A$5:$A$21,"mmmmddyyy")=$A$2&$A $3&A4),$C$5:$C$21) where December is in a2, 1 is on a3 and 2009 is in a4 |
#9
|
|||
|
|||
Date Format and Sumproduct
On Dec 28, 7:44*pm, "T. Valko" wrote:
It would be a lot easier if you were to just enter the whole data (as a date) in one cell: A2 = 12/1/2009 (or 1/12/2009 depending on your local date format) =SUMPRODUCT(--($B$5:$B$21=$A$1),--($A$5:$A$21=$A$2),$C$5:$C$21) -- Biff Microsoft Excel MVP "wx4usa" wrote in message ... On Dec 28, 5:45 pm, "T. Valko" wrote: Try these... Sum by salesperson for month and year: =SUMPRODUCT(--(Sheet1!B1:B20=A1),--(TEXT(Sheet1!A1:A20,"mmmyyyy")=A2&A3),Sheet1!C1:C2 0) Sum for a specific weekday such as Tuesday: =SUMPRODUCT(--(TEXT(Sheet1!A1:A20,"ddd")="Tue"),Sheet1!C1:C20) Or: =SUMPRODUCT(--(WEEKDAY(Sheet1!A1:A20)=n),Sheet1!C1:C20) Where n = the weekday number: 1 = Sun 2 = Mon 3 = Tue 4 = Wed 5 = Thu 6 = Fri 7 = Sat -- Biff Microsoft Excel MVP "wx4usa" wrote in message .... On Dec 28, 4:46 pm, "T. Valko" wrote: On a separate page in the workbook, I have a report with these selection criteria... A1 is salesperson A2 is month A3 is year In A2, how are you entering the month, as the month number or the month name? A2 = 5 (month 5 = May) A2 = May -- Biff Microsoft Excel MVP "wx4usa" wrote in message .... I have a column A for the date MM/DD/YYYY format. I need to have a sumproduct formula look thru the database and return sales totals for salesperson by month, day and year separately. For example: Column A is date B is salesperson C is sales On a separate page in the workbook, I have a report with these selection criteria... A1 is salesperson A2 is month A3 is year I need a formula to return sales for this salesperson for the month and for the year. Looking ahead, the report/database will span several years. Also, is there a way to sumproduct sales by day of the week such as Tuesdays using the mm/dd/yyyy format? I seem to struggle with dates and sumproduct. Any help would be greatly appreciated. I am entering Month Name such as May. Hi Biff How can I also do a specific day such as Dec 1, 2009? *12/01/2009? I tried this and it doesnt work..... =SUMPRODUCT(--($B$5:$B$21=$A$1),--(TEXT($A$5:$A$21,"mmmmddyyy")=$A$2&$A $3&A4),$C$5:$C$21) where December is in a2, 1 is on a3 and 2009 is in a4 Hey Biff, Can I also return sales by my specific salesperson for a date range such as 11/15/2009 thru 12/20/2009? |
#10
|
|||
|
|||
Date Format and Sumproduct
Use cells to hold the date boundaries:
A1 = salesperson A2 = start date A3 = end date =SUMPRODUCT(--($B$5:$B$21=$A$1),--($A$5:$A$21=$A$2),--($A$5:$A$21=$A$3),$C$5:$C$21) -- Biff Microsoft Excel MVP "wx4usa" wrote in message ... On Dec 28, 7:44 pm, "T. Valko" wrote: It would be a lot easier if you were to just enter the whole data (as a date) in one cell: A2 = 12/1/2009 (or 1/12/2009 depending on your local date format) =SUMPRODUCT(--($B$5:$B$21=$A$1),--($A$5:$A$21=$A$2),$C$5:$C$21) -- Biff Microsoft Excel MVP "wx4usa" wrote in message ... On Dec 28, 5:45 pm, "T. Valko" wrote: Try these... Sum by salesperson for month and year: =SUMPRODUCT(--(Sheet1!B1:B20=A1),--(TEXT(Sheet1!A1:A20,"mmmyyyy")=A2&A3),Sheet1!C1:C2 0) Sum for a specific weekday such as Tuesday: =SUMPRODUCT(--(TEXT(Sheet1!A1:A20,"ddd")="Tue"),Sheet1!C1:C20) Or: =SUMPRODUCT(--(WEEKDAY(Sheet1!A1:A20)=n),Sheet1!C1:C20) Where n = the weekday number: 1 = Sun 2 = Mon 3 = Tue 4 = Wed 5 = Thu 6 = Fri 7 = Sat -- Biff Microsoft Excel MVP "wx4usa" wrote in message ... On Dec 28, 4:46 pm, "T. Valko" wrote: On a separate page in the workbook, I have a report with these selection criteria... A1 is salesperson A2 is month A3 is year In A2, how are you entering the month, as the month number or the month name? A2 = 5 (month 5 = May) A2 = May -- Biff Microsoft Excel MVP "wx4usa" wrote in message ... I have a column A for the date MM/DD/YYYY format. I need to have a sumproduct formula look thru the database and return sales totals for salesperson by month, day and year separately. For example: Column A is date B is salesperson C is sales On a separate page in the workbook, I have a report with these selection criteria... A1 is salesperson A2 is month A3 is year I need a formula to return sales for this salesperson for the month and for the year. Looking ahead, the report/database will span several years. Also, is there a way to sumproduct sales by day of the week such as Tuesdays using the mm/dd/yyyy format? I seem to struggle with dates and sumproduct. Any help would be greatly appreciated. I am entering Month Name such as May. Hi Biff How can I also do a specific day such as Dec 1, 2009? 12/01/2009? I tried this and it doesnt work..... =SUMPRODUCT(--($B$5:$B$21=$A$1),--(TEXT($A$5:$A$21,"mmmmddyyy")=$A$2&$A $3&A4),$C$5:$C$21) where December is in a2, 1 is on a3 and 2009 is in a4 Hey Biff, Can I also return sales by my specific salesperson for a date range such as 11/15/2009 thru 12/20/2009? |
Thread Tools | |
Display Modes | |
|
|