sumproduct Formula PLEASE HELP
 

ColumnA Column B ColumnC ColumnD
$2,958.73 Kelly RSP 5-Feb-07
$91,000.00 Sherri Non-Reg 12-Feb-07
$15,648.47 Kelly RRIF 21-Mar-07
These are my columns. I want to make a running total for each salesperson
PER QUARTER. So if their name appears in Column B AND Jan, Feb or Mar
appears in Column D, I want it to add their sales numbers from Column A to
this quarterly total:

Ex) Kelly appeared in Col B AND Feb appeared in Column D so it added
$2958.73 to her total quarterly sales:

Kelly 1st Quarter Sales $18607.2 (total sales for Jan,Feb,Mar)


This is the formula I used and it keeps telling me that there is a problem
with a67 and then send error message #value!
I am inputting this formula into cell b67.
a67 is where Kelly's name appears
b67 is where I want the running total of sales for Kelly for Q1
b2:b100 = list of who made sales
j9 = start date of quarter
j10 = end date of quarter
a2:a100 = sales made per salesperson

=sumproduct((b2:b100=a67)*(d2:d100=J9)*(d2:d100<= j10)*a2:a100)

sumproduct Formula PLEASE HELP
 

"cdw" wrote in message
...
ColumnA Column B ColumnC ColumnD
$2,958.73 Kelly RSP 5-Feb-07
$91,000.00 Sherri Non-Reg 12-Feb-07
$15,648.47 Kelly RRIF 21-Mar-07
These are my columns. I want to make a running total for each
salesperson
PER QUARTER. So if their name appears in Column B AND Jan, Feb or Mar
appears in Column D, I want it to add their sales numbers from Column A
to
this quarterly total:

Ex) Kelly appeared in Col B AND Feb appeared in Column D so it added
$2958.73 to her total quarterly sales:

Kelly 1st Quarter Sales $18607.2 (total sales for
Jan,Feb,Mar)


This is the formula I used and it keeps telling me that there is a problem
with a67 and then send error message #value!
I am inputting this formula into cell b67.
a67 is where Kelly's name appears
b67 is where I want the running total of sales for Kelly for Q1
b2:b100 = list of who made sales
j9 = start date of quarter
j10 = end date of quarter
a2:a100 = sales made per salesperson

=sumproduct((b2:b100=a67)*(d2:d100=J9)*(d2:d100<= j10)*a2:a100)

First you say that "a67 is where Kelly's name appears"
Then you say "a2:a100 = sales made per salesperson"
As a67 is within the range a2:a100, both of your statements cannot be
correct. I suspect this is where your problem lies.

sumproduct Formula PLEASE HELP
 

So the suggestion in yourother post didn't work then?

"cdw" wrote:

ColumnA Column B ColumnC ColumnD
$2,958.73 Kelly RSP 5-Feb-07
$91,000.00 Sherri Non-Reg 12-Feb-07
$15,648.47 Kelly RRIF 21-Mar-07
These are my columns. I want to make a running total for each salesperson
PER QUARTER. So if their name appears in Column B AND Jan, Feb or Mar
appears in Column D, I want it to add their sales numbers from Column A to
this quarterly total:

Ex) Kelly appeared in Col B AND Feb appeared in Column D so it added
$2958.73 to her total quarterly sales:

Kelly 1st Quarter Sales $18607.2 (total sales for Jan,Feb,Mar)


This is the formula I used and it keeps telling me that there is a problem
with a67 and then send error message #value!
I am inputting this formula into cell b67.
a67 is where Kelly's name appears
b67 is where I want the running total of sales for Kelly for Q1
b2:b100 = list of who made sales
j9 = start date of quarter
j10 = end date of quarter
a2:a100 = sales made per salesperson

=sumproduct((b2:b100=a67)*(d2:d100=J9)*(d2:d100<= j10)*a2:a100)

sumproduct Formula PLEASE HELP
 

Maybe you mean something like this instead in say, F67:
=SUMPRODUCT((B2:B100=E67)*(D2:D100=J9)*(D2:D100<= J10)*A2:A100)
where E67 contains the name: Kelly

A2:A100 contains the sales numbers,
and the name: Kelly should not be there in A67

B2:B100 contains the names list,
and the formula should not be placed in B67
which would cause circular ref errors.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"cdw" wrote:
ColumnA Column B ColumnC ColumnD
$2,958.73 Kelly RSP 5-Feb-07
$91,000.00 Sherri Non-Reg 12-Feb-07
$15,648.47 Kelly RRIF 21-Mar-07
These are my columns. I want to make a running total for each salesperson
PER QUARTER. So if their name appears in Column B AND Jan, Feb or Mar
appears in Column D, I want it to add their sales numbers from Column A to
this quarterly total:

Ex) Kelly appeared in Col B AND Feb appeared in Column D so it added
$2958.73 to her total quarterly sales:

Kelly 1st Quarter Sales $18607.2 (total sales for Jan,Feb,Mar)


This is the formula I used and it keeps telling me that there is a problem
with a67 and then send error message #value!
I am inputting this formula into cell b67.
a67 is where Kelly's name appears
b67 is where I want the running total of sales for Kelly for Q1
b2:b100 = list of who made sales
j9 = start date of quarter
j10 = end date of quarter
a2:a100 = sales made per salesperson

=sumproduct((b2:b100=a67)*(d2:d100=J9)*(d2:d100<= j10)*a2:a100)

sumproduct Formula PLEASE HELP
 

=SUMPRODUCT(--(B2:B200="Kelly"),--(YEAR(D2:D200)=2007),--(MONTH(D2:D200)<=3),A2:A200)


For months Apr - Jun


=SUMPRODUCT(--(B2:B200="Kelly"),--(YEAR(D2:D200)=2007),--(MONTH(D2:D200)=4),--(MONTH(D2:D200)<=6),A2:A200)


Also you might want to put the sales people criteria in a cell

=SUMPRODUCT(--(B2:B200=E1),--(YEAR(D2:D200)=2007),--(MONTH(D2:D200)=4),--(MONTH(D2:D200)<=6),A2:A200)

Where you would put Kelly in E1


You could also use

=SUMPRODUCT(--(B2:B200=E1),--(YEAR(D2:D200)=2007),--(ISNUMBER(MATCH(MONTH(D2:D200),{1,2,3},0))),A2:A20 0)

where {1,2,3}

is Jan-Mar



--


Regards,


Peo Sjoblom


"cdw" wrote in message
...
ColumnA Column B ColumnC ColumnD
$2,958.73 Kelly RSP 5-Feb-07
$91,000.00 Sherri Non-Reg 12-Feb-07
$15,648.47 Kelly RRIF 21-Mar-07
These are my columns. I want to make a running total for each
salesperson
PER QUARTER. So if their name appears in Column B AND Jan, Feb or Mar
appears in Column D, I want it to add their sales numbers from Column A
to
this quarterly total:

Ex) Kelly appeared in Col B AND Feb appeared in Column D so it added
$2958.73 to her total quarterly sales:

Kelly 1st Quarter Sales $18607.2 (total sales for
Jan,Feb,Mar)


This is the formula I used and it keeps telling me that there is a problem
with a67 and then send error message #value!
I am inputting this formula into cell b67.
a67 is where Kelly's name appears
b67 is where I want the running total of sales for Kelly for Q1
b2:b100 = list of who made sales
j9 = start date of quarter
j10 = end date of quarter
a2:a100 = sales made per salesperson

=sumproduct((b2:b100=a67)*(d2:d100=J9)*(d2:d100<= j10)*a2:a100)

sumproduct Formula PLEASE HELP
 

AFAICS the other Kelly item is also in the firstw quarter, so the amount
should be $18,607.20, and that is what your formula gives.

Make sure column A is proper amounts, not text.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"cdw" wrote in message
...
ColumnA Column B ColumnC ColumnD
$2,958.73 Kelly RSP 5-Feb-07
$91,000.00 Sherri Non-Reg 12-Feb-07
$15,648.47 Kelly RRIF 21-Mar-07
These are my columns. I want to make a running total for each
salesperson
PER QUARTER. So if their name appears in Column B AND Jan, Feb or Mar
appears in Column D, I want it to add their sales numbers from Column A
to
this quarterly total:

Ex) Kelly appeared in Col B AND Feb appeared in Column D so it added
$2958.73 to her total quarterly sales:

Kelly 1st Quarter Sales $18607.2 (total sales for
Jan,Feb,Mar)


This is the formula I used and it keeps telling me that there is a problem
with a67 and then send error message #value!
I am inputting this formula into cell b67.
a67 is where Kelly's name appears
b67 is where I want the running total of sales for Kelly for Q1
b2:b100 = list of who made sales
j9 = start date of quarter
j10 = end date of quarter
a2:a100 = sales made per salesperson

=sumproduct((b2:b100=a67)*(d2:d100=J9)*(d2:d100<= j10)*a2:a100)

sumproduct Formula PLEASE HELP
 

I edited and reposted question. Please try to help as this did not work.

"Stephen" wrote:

"cdw" wrote in message
...
ColumnA Column B ColumnC ColumnD
$2,958.73 Kelly RSP 5-Feb-07
$91,000.00 Sherri Non-Reg 12-Feb-07
$15,648.47 Kelly RRIF 21-Mar-07
These are my columns. I want to make a running total for each
salesperson
PER QUARTER. So if their name appears in Column B AND Jan, Feb or Mar
appears in Column D, I want it to add their sales numbers from Column A
to
this quarterly total:

Ex) Kelly appeared in Col B AND Feb appeared in Column D so it added
$2958.73 to her total quarterly sales:

Kelly 1st Quarter Sales $18607.2 (total sales for
Jan,Feb,Mar)


This is the formula I used and it keeps telling me that there is a problem
with a67 and then send error message #value!
I am inputting this formula into cell b67.
a67 is where Kelly's name appears
b67 is where I want the running total of sales for Kelly for Q1
b2:b100 = list of who made sales
j9 = start date of quarter
j10 = end date of quarter
a2:a100 = sales made per salesperson

=sumproduct((b2:b100=a67)*(d2:d100=J9)*(d2:d100<= j10)*a2:a100)

First you say that "a67 is where Kelly's name appears"
Then you say "a2:a100 = sales made per salesperson"
As a67 is within the range a2:a100, both of your statements cannot be
correct. I suspect this is where your problem lies.

sumproduct Formula PLEASE HELP
 

I edited my spreadsheet and reposted question as this did not work. Please
check for new post and try to help. Thanks

"Peo Sjoblom" wrote:

=SUMPRODUCT(--(B2:B200="Kelly"),--(YEAR(D2:D200)=2007),--(MONTH(D2:D200)<=3),A2:A200)


For months Apr - Jun


=SUMPRODUCT(--(B2:B200="Kelly"),--(YEAR(D2:D200)=2007),--(MONTH(D2:D200)=4),--(MONTH(D2:D200)<=6),A2:A200)


Also you might want to put the sales people criteria in a cell

=SUMPRODUCT(--(B2:B200=E1),--(YEAR(D2:D200)=2007),--(MONTH(D2:D200)=4),--(MONTH(D2:D200)<=6),A2:A200)

Where you would put Kelly in E1


You could also use

=SUMPRODUCT(--(B2:B200=E1),--(YEAR(D2:D200)=2007),--(ISNUMBER(MATCH(MONTH(D2:D200),{1,2,3},0))),A2:A20 0)

where {1,2,3}

is Jan-Mar



--


Regards,


Peo Sjoblom


"cdw" wrote in message
...
ColumnA Column B ColumnC ColumnD
$2,958.73 Kelly RSP 5-Feb-07
$91,000.00 Sherri Non-Reg 12-Feb-07
$15,648.47 Kelly RRIF 21-Mar-07
These are my columns. I want to make a running total for each
salesperson
PER QUARTER. So if their name appears in Column B AND Jan, Feb or Mar
appears in Column D, I want it to add their sales numbers from Column A
to
this quarterly total:

Ex) Kelly appeared in Col B AND Feb appeared in Column D so it added
$2958.73 to her total quarterly sales:

Kelly 1st Quarter Sales $18607.2 (total sales for
Jan,Feb,Mar)


This is the formula I used and it keeps telling me that there is a problem
with a67 and then send error message #value!
I am inputting this formula into cell b67.
a67 is where Kelly's name appears
b67 is where I want the running total of sales for Kelly for Q1
b2:b100 = list of who made sales
j9 = start date of quarter
j10 = end date of quarter
a2:a100 = sales made per salesperson

=sumproduct((b2:b100=a67)*(d2:d100=J9)*(d2:d100<= j10)*a2:a100)