Sum Product Function Question

[Guest]

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

 

[Domenic]

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!

 

[Domenic]

That should be...

1/1/05 (January 1, 2005)

2/1/05 (February 1, 2005)

3/1/05 (March 1, 2005)

....and so on.

 

[Guest]

=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).

 

[Guest]

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

 

[Ron Rosenfeld]

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]

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

 

[Guest]

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

 

[Dave Peterson]

It usually means that not every cell in that range (column) is a date. Look for
blanks and text.

 

[Ron Rosenfeld]

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

 

[Guest]

Thank you so much! That was exactly what I needed!