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
|