Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nesting a sumproduct formula within a sumif formula. | Excel Discussion (Misc queries) | |||
sumproduct formula | Excel Worksheet Functions | |||
sumproduct formula | Excel Discussion (Misc queries) | |||
sumproduct or other formula? | Excel Discussion (Misc queries) | |||
Help On SUMPRODUCT Formula | Excel Worksheet Functions |