|
Sum Product Function Question
Hello All:
Quick question on SumProduct... Sheet 1 is a recap of processed returns for customers A1=Sprint A2=Verizon Column B is January Column C is February, and so on Sheet 2 is a "tracking log" of returns Column A contains the Date value, including month & year A1 = January 13 - E1 contains "Sprint" A2 = February 2 - E2 contains "Verizon" A3 = March - E3 contains "Sprint" I would like Sheet 1 to reflect the number of transactions within a specific month per customer, that is to say.... Jan Feb Mar Sprint 1 1 Verizon 1 All suggestions welcome! Thanks! Sandi |
Assumptions:
Sheet2... Column A contains true date values Sheet1... Column A, starting at A2, contains the customer The first row, starting at B1, contains the month entered in the following manner... 1/1/2005 1/2/2005 1/3/2005 ....and custom formatted as "mmmm" Formula: Sheet1... B2, copied across and down: =SUMPRODUCT(--(Sheet2!$A$1:$A$10-DAY(Sheet2!$A$1:$A$10)+1=B$1),--(Sheet2! $E$1:$E$10=$A2)) Hope this helps! In article , RUSH2CROCHET wrote: Hello All: Quick question on SumProduct... Sheet 1 is a recap of processed returns for customers A1=Sprint A2=Verizon Column B is January Column C is February, and so on Sheet 2 is a "tracking log" of returns Column A contains the Date value, including month & year A1 = January 13 - E1 contains "Sprint" A2 = February 2 - E2 contains "Verizon" A3 = March - E3 contains "Sprint" I would like Sheet 1 to reflect the number of transactions within a specific month per customer, that is to say.... Jan Feb Mar Sprint 1 1 Verizon 1 All suggestions welcome! Thanks! Sandi |
That should be...
1/1/05 (January 1, 2005) 2/1/05 (February 1, 2005) 3/1/05 (March 1, 2005) ....and so on. In article , Domenic wrote: The first row, starting at B1, contains the month entered in the following manner... 1/1/2005 1/2/2005 1/3/2005 ...and custom formatted as "mmmm" |
=SUMPRODUCT(--(MONTH(Sheet2!$A$1:$A$3)=B$1),--(Sheet2!$E$1:$E$3=$A2))
Assuming B1 contains the month (in number format Jan =1, Feb =2, etc) and A2 contains the service provider. Change the range references for Sheet2 as needed. Alternatively: =SUMPRODUCT(--(TEXT(Sheet2!$A$1:$A$3,"MMM")=B$1),--(Sheet2!E$1:$E$3=$A2)) using same assumptions as above, but using "Jan" "Feb", etc (text format instead of numeric representation). "RUSH2CROCHET" wrote: Hello All: Quick question on SumProduct... Sheet 1 is a recap of processed returns for customers A1=Sprint A2=Verizon Column B is January Column C is February, and so on Sheet 2 is a "tracking log" of returns Column A contains the Date value, including month & year A1 = January 13 - E1 contains "Sprint" A2 = February 2 - E2 contains "Verizon" A3 = March - E3 contains "Sprint" I would like Sheet 1 to reflect the number of transactions within a specific month per customer, that is to say.... Jan Feb Mar Sprint 1 1 Verizon 1 All suggestions welcome! Thanks! Sandi |
Perhaps, if I redefine, my question might be clearer....
1 SHEET 2 (Otherwise known as "Relocation Master Log" Col. A B C D E Date Format Date Model S/N Acct Code of Column B mm/dd/yy __________________________________________________ __ 38353 01/01/05 stk123 123456 D1E 38401 02/18/05 stk456 789123 X47 38426 03/15/05 stk123 654321 D1E The above table shows that "D1E" had 2 transactions-1 each in January & March, and "X47" had 1 transaction in February. Following is what I would like SHEET 1 (Otherwise known as "Monthly Totals) to display: Col A B C D E F Customer Code Jan Feb Mar Apr May _____________________________________________ D1E 1 1 X47 1 Additionally, I would like to continue this tracking log beyond the current year, so my sum product should look at the year value from Sheet 2-Column A. I have tried the following,using both normal & array entry, but cannot get it to calculate properly. =sumproduct((Year('Relocation Master Log '!$a:$a)=2005)*((Month('Relocation Master Log '!$a:$a)=1)*('Relocation Master Log '!$e:$e)="D1E") All suggestions welcome &Thanks to all! Sandi "JMB" wrote: =SUMPRODUCT(--(MONTH(Sheet2!$A$1:$A$3)=B$1),--(Sheet2!$E$1:$E$3=$A2)) Assuming B1 contains the month (in number format Jan =1, Feb =2, etc) and A2 contains the service provider. Change the range references for Sheet2 as needed. Alternatively: =SUMPRODUCT(--(TEXT(Sheet2!$A$1:$A$3,"MMM")=B$1),--(Sheet2!E$1:$E$3=$A2)) using same assumptions as above, but using "Jan" "Feb", etc (text format instead of numeric representation). "RUSH2CROCHET" wrote: Hello All: Quick question on SumProduct... Sheet 1 is a recap of processed returns for customers A1=Sprint A2=Verizon Column B is January Column C is February, and so on Sheet 2 is a "tracking log" of returns Column A contains the Date value, including month & year A1 = January 13 - E1 contains "Sprint" A2 = February 2 - E2 contains "Verizon" A3 = March - E3 contains "Sprint" I would like Sheet 1 to reflect the number of transactions within a specific month per customer, that is to say.... Jan Feb Mar Sprint 1 1 Verizon 1 All suggestions welcome! Thanks! Sandi |
On Thu, 6 Oct 2005 08:40:09 -0700, RUSH2CROCHET
wrote: Perhaps, if I redefine, my question might be clearer.... 1 SHEET 2 (Otherwise known as "Relocation Master Log" Col. A B C D E Date Format Date Model S/N Acct Code of Column B mm/dd/yy _________________________________________________ ___ 38353 01/01/05 stk123 123456 D1E 38401 02/18/05 stk456 789123 X47 38426 03/15/05 stk123 654321 D1E The above table shows that "D1E" had 2 transactions-1 each in January & March, and "X47" had 1 transaction in February. Following is what I would like SHEET 1 (Otherwise known as "Monthly Totals) to display: Col A B C D E F Customer Code Jan Feb Mar Apr May _____________________________________________ D1E 1 1 X47 1 Additionally, I would like to continue this tracking log beyond the current year, so my sum product should look at the year value from Sheet 2-Column A. Try a Pivot Table. Put your column labels in the row above your data. Then select the range that includes the column labels and data in columns B:E Data/Pivot Table .... Drag Date to the Column area; Acct Code to the Row area and Acct Code to the data area. Right click on the Date label in the pivot table, and select Group and Show Detail/Group By Months. Choose or devise an attractive format. --ron |
You may want to try a pivottable.
Put your headers in one row--use alt-enters to force new lines within the cell. Select your range -- include the headers data|pivottable follow the wizard until you get to a step that has a Layout button on it. Click that Layout button Drag the Date (column B version) "button" to the column area drag the Acct "button" to the row area drag the acct (yep, again) to the data area It should say "count of". If it doesn't, just double click on that data area acct button and choose Count. And finish the wizard. But now you have individual dates in the header. You can group them per month (and year). Right click on the Date button and choose "group and show detail", then "group". Choose Months and years (unless you want jan of all years grouped together???). When I did it, I got something that looked like: Count of acct Years Date 2005 Grand Total acct Jan Feb Mar D1E 1 1 2 X47 1 1 Grand Total 1 1 1 3 ======== If you're going to add more rows to the table, you may want to use a dynamic range name for that pivottable range. Debra Dalgleish explains it: http://contextures.com/xlNames01.html#Dynamic RUSH2CROCHET wrote: Perhaps, if I redefine, my question might be clearer.... 1 SHEET 2 (Otherwise known as "Relocation Master Log" Col. A B C D E Date Format Date Model S/N Acct Code of Column B mm/dd/yy __________________________________________________ __ 38353 01/01/05 stk123 123456 D1E 38401 02/18/05 stk456 789123 X47 38426 03/15/05 stk123 654321 D1E The above table shows that "D1E" had 2 transactions-1 each in January & March, and "X47" had 1 transaction in February. Following is what I would like SHEET 1 (Otherwise known as "Monthly Totals) to display: Col A B C D E F Customer Code Jan Feb Mar Apr May _____________________________________________ D1E 1 1 X47 1 Additionally, I would like to continue this tracking log beyond the current year, so my sum product should look at the year value from Sheet 2-Column A. I have tried the following,using both normal & array entry, but cannot get it to calculate properly. =sumproduct((Year('Relocation Master Log '!$a:$a)=2005)*((Month('Relocation Master Log '!$a:$a)=1)*('Relocation Master Log '!$e:$e)="D1E") All suggestions welcome &Thanks to all! Sandi "JMB" wrote: =SUMPRODUCT(--(MONTH(Sheet2!$A$1:$A$3)=B$1),--(Sheet2!$E$1:$E$3=$A2)) Assuming B1 contains the month (in number format Jan =1, Feb =2, etc) and A2 contains the service provider. Change the range references for Sheet2 as needed. Alternatively: =SUMPRODUCT(--(TEXT(Sheet2!$A$1:$A$3,"MMM")=B$1),--(Sheet2!E$1:$E$3=$A2)) using same assumptions as above, but using "Jan" "Feb", etc (text format instead of numeric representation). "RUSH2CROCHET" wrote: Hello All: Quick question on SumProduct... Sheet 1 is a recap of processed returns for customers A1=Sprint A2=Verizon Column B is January Column C is February, and so on Sheet 2 is a "tracking log" of returns Column A contains the Date value, including month & year A1 = January 13 - E1 contains "Sprint" A2 = February 2 - E2 contains "Verizon" A3 = March - E3 contains "Sprint" I would like Sheet 1 to reflect the number of transactions within a specific month per customer, that is to say.... Jan Feb Mar Sprint 1 1 Verizon 1 All suggestions welcome! Thanks! Sandi -- Dave Peterson |
Ron:
I don't know why a pivot table never even entered my head. One problem though, I get "Cannot group that selection", when I right click on the date field? Any ideas....? Thanks again! Sandi "Ron Rosenfeld" wrote: On Thu, 6 Oct 2005 08:40:09 -0700, RUSH2CROCHET wrote: Perhaps, if I redefine, my question might be clearer.... 1 SHEET 2 (Otherwise known as "Relocation Master Log" Col. A B C D E Date Format Date Model S/N Acct Code of Column B mm/dd/yy _________________________________________________ ___ 38353 01/01/05 stk123 123456 D1E 38401 02/18/05 stk456 789123 X47 38426 03/15/05 stk123 654321 D1E The above table shows that "D1E" had 2 transactions-1 each in January & March, and "X47" had 1 transaction in February. Following is what I would like SHEET 1 (Otherwise known as "Monthly Totals) to display: Col A B C D E F Customer Code Jan Feb Mar Apr May _____________________________________________ D1E 1 1 X47 1 Additionally, I would like to continue this tracking log beyond the current year, so my sum product should look at the year value from Sheet 2-Column A. Try a Pivot Table. Put your column labels in the row above your data. Then select the range that includes the column labels and data in columns B:E Data/Pivot Table .... Drag Date to the Column area; Acct Code to the Row area and Acct Code to the data area. Right click on the Date label in the pivot table, and select Group and Show Detail/Group By Months. Choose or devise an attractive format. --ron |
It usually means that not every cell in that range (column) is a date. Look for
blanks and text. RUSH2CROCHET wrote: Ron: I don't know why a pivot table never even entered my head. One problem though, I get "Cannot group that selection", when I right click on the date field? Any ideas....? Thanks again! Sandi "Ron Rosenfeld" wrote: On Thu, 6 Oct 2005 08:40:09 -0700, RUSH2CROCHET wrote: Perhaps, if I redefine, my question might be clearer.... 1 SHEET 2 (Otherwise known as "Relocation Master Log" Col. A B C D E Date Format Date Model S/N Acct Code of Column B mm/dd/yy _________________________________________________ ___ 38353 01/01/05 stk123 123456 D1E 38401 02/18/05 stk456 789123 X47 38426 03/15/05 stk123 654321 D1E The above table shows that "D1E" had 2 transactions-1 each in January & March, and "X47" had 1 transaction in February. Following is what I would like SHEET 1 (Otherwise known as "Monthly Totals) to display: Col A B C D E F Customer Code Jan Feb Mar Apr May _____________________________________________ D1E 1 1 X47 1 Additionally, I would like to continue this tracking log beyond the current year, so my sum product should look at the year value from Sheet 2-Column A. Try a Pivot Table. Put your column labels in the row above your data. Then select the range that includes the column labels and data in columns B:E Data/Pivot Table .... Drag Date to the Column area; Acct Code to the Row area and Acct Code to the data area. Right click on the Date label in the pivot table, and select Group and Show Detail/Group By Months. Choose or devise an attractive format. --ron -- Dave Peterson |
On Thu, 6 Oct 2005 12:13:54 -0700, RUSH2CROCHET
wrote: Ron: I don't know why a pivot table never even entered my head. One problem though, I get "Cannot group that selection", when I right click on the date field? Any ideas....? Thanks again! Sandi Check that all your entries in that field are Excel dates (not blanks or text) --ron |
Thank you so much! That was exactly what I needed!
"Dave Peterson" wrote: It usually means that not every cell in that range (column) is a date. Look for blanks and text. RUSH2CROCHET wrote: Ron: I don't know why a pivot table never even entered my head. One problem though, I get "Cannot group that selection", when I right click on the date field? Any ideas....? Thanks again! Sandi "Ron Rosenfeld" wrote: On Thu, 6 Oct 2005 08:40:09 -0700, RUSH2CROCHET wrote: Perhaps, if I redefine, my question might be clearer.... 1 SHEET 2 (Otherwise known as "Relocation Master Log" Col. A B C D E Date Format Date Model S/N Acct Code of Column B mm/dd/yy _________________________________________________ ___ 38353 01/01/05 stk123 123456 D1E 38401 02/18/05 stk456 789123 X47 38426 03/15/05 stk123 654321 D1E The above table shows that "D1E" had 2 transactions-1 each in January & March, and "X47" had 1 transaction in February. Following is what I would like SHEET 1 (Otherwise known as "Monthly Totals) to display: Col A B C D E F Customer Code Jan Feb Mar Apr May _____________________________________________ D1E 1 1 X47 1 Additionally, I would like to continue this tracking log beyond the current year, so my sum product should look at the year value from Sheet 2-Column A. Try a Pivot Table. Put your column labels in the row above your data. Then select the range that includes the column labels and data in columns B:E Data/Pivot Table .... Drag Date to the Column area; Acct Code to the Row area and Acct Code to the data area. Right click on the Date label in the pivot table, and select Group and Show Detail/Group By Months. Choose or devise an attractive format. --ron -- Dave Peterson |
| All times are GMT +1. The time now is 01:56 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com