If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Converting a SUMPRODUCT formula to COUNTA active...
I'm having problems with a schedule where if users nominate "Job start" on a row, they need to enter dates instead of general numbers into the schedule eg they might enter a 12 in the W/C 7/3/04 column to identify the 12th March.
I need the formula to take 12 and count it as 1 then multiply it by the cost in CZ20 ($1000). When "Job Start" or dates are not entered the following formula works fine: =SUMPRODUCT(--(MONTH($O$11:$BN$11)=MONTH(EK$11)),$O20:$BN20)*CZ2 0) When dates are entered the formula does not work - it needs to count the active cells instead of summing them. I tried to add the COUNTA function to the formula but it did not like it. I think need a different way to calculate the data? Two solutions were suggested but neither is working: 1. Exclude the O20:BN20 range. But then the formula only calculates against the number of weeks in the month instead of the activity resulting in $4000 for the month of March?? =IF(A20="Job start",SUMPRODUCT(--(MONTH($O$11:$BN$11)=MONTH(EK$11)))*CZ20,SUMPRODUC T(--(MONTH($O$11:$BN$11)=MONTH(EK$11)),$O20:$BN20)*CZ2 0)) 2. To divide the month result by the month (???) to get a count, this definitely didn't work - again the current row of activity is not included, plus the month number (eg 3 for March) has no relationship with the changing number of insertions entered into (O20:BN20) (ie neither 12 or 1 in W/C 7/3/04 works). The result here was $1333??? =IF(A20="Job start",SUMPRODUCT(--(MONTH($O$11:$BN$11)=MONTH(EK$11)))/MONTH(EK$11)*CZ20,SUMPRODUCT(--(MONTH($O$11:$BN$11)=MONTH(EK$11)),$O20:$BN20)*CZ2 0)) If anyone can advise on what formula I should use here I would greatly appreciate it. TFYH |
#3
|
|||
|
|||
Converting a SUMPRODUCT formula to COUNTA active...
I'm sorry Don - I think I'm a bit dense but I can't relate the ranges you quoted to what I'm needing and when I tried the three you gave me none of them counted my active cells so I'm obviously still getting it wrong??
Could you give me it again but this time using titles like below so I can understand which ranges belong in what spots. "lookup month" ie Mar-04 or cell EJ11 "Date Range" or cells O11:BN11 "activity row" "activity" or cells O20:BN20 (1 insertion is entered as a "12" and needs to be counted once) "rate" the cost to multiply to active cells by Thanks muchly BeSmart (not today - hopeful but not successful) ----- Don Guillett wrote: ----- Without getting into the detail, in general sumproduct can count or sum if one =sumproduct((month(rngA=1)*1) or if 2 criteria =sumproduct((month(rngA=1)*(rngB="joe")) counts em =sumproduct((month(rngA=1)*(rngB="joe")*rngC) sums rngC for the criteria -- Don Guillett SalesAid Software "BeSmart" wrote in message ... I'm having problems with a schedule where if users nominate "Job start" on a row, they need to enter dates instead of general numbers into the schedule eg they might enter a 12 in the W/C 7/3/04 column to identify the 12th March. I need the formula to take 12 and count it as 1 then multiply it by the cost in CZ20 ($1000). When "Job Start" or dates are not entered the following formula works fine: =SUMPRODUCT(--(MONTH($O$11:$BN$11)=MONTH(EK$11)),$O20:$BN20)*CZ2 0) When dates are entered the formula does not work - it needs to count the active cells instead of summing them. I tried to add the COUNTA function to the formula but it did not like it. I think need a different way to calculate the data? Two solutions were suggested but neither is working: 1. Exclude the O20:BN20 range. But then the formula only calculates against the number of weeks in the month instead of the activity resulting in $4000 for the month of March?? =IF(A20="Job start",SUMPRODUCT(--(MONTH($O$11:$BN$11)=MONTH(EK$11)))*CZ20,SUMPRODUC T(--(M ONTH($O$11:$BN$11)=MONTH(EK$11)),$O20:$BN20)*CZ20) ) 2. To divide the month result by the month (???) to get a count, this definitely didn't work - again the current row of activity is not included, plus the month number (eg 3 for March) has no relationship with the changing number of insertions entered into (O20:BN20) (ie neither 12 or 1 in W/C 7/3/04 works). The result here was $1333??? =IF(A20="Job start",SUMPRODUCT(--(MONTH($O$11:$BN$11)=MONTH(EK$11)))/MONTH(EK$11)*CZ20,SU MPRODUCT(--(MONTH($O$11:$BN$11)=MONTH(EK$11)),$O20:$BN20)*CZ2 0)) If anyone can advise on what formula I should use here I would greatly appreciate it. TFYH |
#4
|
|||
|
|||
Converting a SUMPRODUCT formula to COUNTA active...
Hi
try =SUMPRODUCT(--(MONTH($O$11:$BN$11)=MONTH(EK$11))*($O20:$BN20=12) )*CZ20 -- Regards Frank Kabel Frankfurt, Germany BeSmart wrote: I'm having problems with a schedule where if users nominate "Job start" on a row, they need to enter dates instead of general numbers into the schedule eg they might enter a 12 in the W/C 7/3/04 column to identify the 12th March. I need the formula to take 12 and count it as 1 then multiply it by the cost in CZ20 ($1000). When "Job Start" or dates are not entered the following formula works fine: =SUMPRODUCT(--(MONTH($O$11:$BN$11)=MONTH(EK$11)),$O20:$BN20)*CZ2 0) When dates are entered the formula does not work - it needs to count the active cells instead of summing them. I tried to add the COUNTA function to the formula but it did not like it. I think need a different way to calculate the data? Two solutions were suggested but neither is working: 1. Exclude the O20:BN20 range. But then the formula only calculates against the number of weeks in the month instead of the activity resulting in $4000 for the month of March?? =IF(A20="Job start",SUMPRODUCT(--(MONTH($O$11:$BN$11)=MONTH(EK$11)))*CZ20,SUMPRODUC T (--(MONTH($O$11:$BN$11)=MONTH(EK$11)),$O20:$BN20)*CZ2 0)) 2. To divide the month result by the month (???) to get a count, this definitely didn't work - again the current row of activity is not included, plus the month number (eg 3 for March) has no relationship with the changing number of insertions entered into (O20:BN20) (ie neither 12 or 1 in W/C 7/3/04 works). The result here was $1333??? =IF(A20="Job start",SUMPRODUCT(--(MONTH($O$11:$BN$11)=MONTH(EK$11)))/MONTH(EK$11)*CZ 20,SUMPRODUCT(--(MONTH($O$11:$BN$11)=MONTH(EK$11)),$O20:$BN20)*CZ2 0)) If anyone can advise on what formula I should use here I would greatly appreciate it. TFYH |
#5
|
|||
|
|||
Converting a SUMPRODUCT formula to COUNTA active...
Thanks Muchly Frank That worked well if the user enters 12 but that number could be any number between 1-31 (ie any date in a month) Any suggestions? BeSmart.. ----- Frank Kabel wrote: ----- Hi try =SUMPRODUCT(--(MONTH($O$11:$BN$11)=MONTH(EK$11))*($O20:$BN20=12) )*CZ20 -- Regards Frank Kabel Frankfurt, Germany BeSmart wrote: I'm having problems with a schedule where if users nominate "Job start" on a row, they need to enter dates instead of general numbers into the schedule eg they might enter a 12 in the W/C 7/3/04 column to identify the 12th March. I need the formula to take 12 and count it as 1 then multiply it by the cost in CZ20 ($1000). When "Job Start" or dates are not entered the following formula works fine: =SUMPRODUCT(--(MONTH($O$11:$BN$11)=MONTH(EK$11)),$O20:$BN20)*CZ2 0) When dates are entered the formula does not work - it needs to count the active cells instead of summing them. I tried to add the COUNTA function to the formula but it did not like it. I think need a different way to calculate the data? Two solutions were suggested but neither is working: 1. Exclude the O20:BN20 range. But then the formula only calculates against the number of weeks in the month instead of the activity resulting in $4000 for the month of March?? =IF(A20="Job start",SUMPRODUCT(--(MONTH($O$11:$BN$11)=MONTH(EK$11)))*CZ20,SUMPRODUC T (--(MONTH($O$11:$BN$11)=MONTH(EK$11)),$O20:$BN20)*CZ2 0)) 2. To divide the month result by the month (???) to get a count, this definitely didn't work - again the current row of activity is not included, plus the month number (eg 3 for March) has no relationship with the changing number of insertions entered into (O20:BN20) (ie neither 12 or 1 in W/C 7/3/04 works). The result here was $1333??? =IF(A20="Job start",SUMPRODUCT(--(MONTH($O$11:$BN$11)=MONTH(EK$11)))/MONTH(EK$11)*CZ 20,SUMPRODUCT(--(MONTH($O$11:$BN$11)=MONTH(EK$11)),$O20:$BN20)*CZ2 0)) If anyone can advise on what formula I should use here I would greatly appreciate it. TFYH |
#6
|
|||
|
|||
Converting a SUMPRODUCT formula to COUNTA active...
Hi
try =SUMPRODUCT(--(MONTH($O$11:$BN$11)=MONTH(EK$11))*ISNUMBER($O20:$ BN20))* CZ20 -- Regards Frank Kabel Frankfurt, Germany BeSmart wrote: Thanks Muchly Frank That worked well if the user enters 12 but that number could be any number between 1-31 (ie any date in a month) Any suggestions? BeSmart.. ----- Frank Kabel wrote: ----- Hi try =SUMPRODUCT(--(MONTH($O$11:$BN$11)=MONTH(EK$11))*($O20:$BN20=12) )*CZ20 -- Regards Frank Kabel Frankfurt, Germany BeSmart wrote: I'm having problems with a schedule where if users nominate "Job start" on a row, they need to enter dates instead of general numbers into the schedule eg they might enter a 12 in the W/C 7/3/04 column to identify the 12th March. I need the formula to take 12 and count it as 1 then multiply it by the cost in CZ20 ($1000). When "Job Start" or dates are not entered the following formula works fine: =SUMPRODUCT(--(MONTH($O$11:$BN$11)=MONTH(EK$11)),$O20:$BN20)*CZ2 0) When dates are entered the formula does not work - it needs to count the active cells instead of summing them. I tried to add the COUNTA function to the formula but it did not like it. I think need a different way to calculate the data? Two solutions were suggested but neither is working: 1. Exclude the O20:BN20 range. But then the formula only calculates against the number of weeks in the month instead of the activity resulting in $4000 for the month of March?? =IF(A20="Job start",SUMPRODUCT(--(MONTH($O$11:$BN$11)=MONTH(EK$11)))*CZ20,SUMPRODUC T (--(MONTH($O$11:$BN$11)=MONTH(EK$11)),$O20:$BN20)*CZ2 0)) 2. To divide the month result by the month (???) to get a count, this definitely didn't work - again the current row of activity is not included, plus the month number (eg 3 for March) has no relationship with the changing number of insertions entered into (O20:BN20) (ie neither 12 or 1 in W/C 7/3/04 works). The result here was $1333??? =IF(A20="Job start",SUMPRODUCT(--(MONTH($O$11:$BN$11)=MONTH(EK$11)))/MONTH(EK$11)*CZ 20,SUMPRODUCT(--(MONTH($O$11:$BN$11)=MONTH(EK$11)),$O20:$BN20)*CZ2 0)) If anyone can advise on what formula I should use here I would greatly appreciate it. TFYH |
#7
|
|||
|
|||
Converting a SUMPRODUCT formula to COUNTA active...
Hi
thnaks for the feedback. guess it's near meadnight in NZ right now :-) -- Regards Frank Kabel Frankfurt, Germany BeSmart wrote: BRILLIANT!!!! Thanks heaps Frank - that's had me stumped all day!!! (I'm in NZ so your night is my day) Cheers BeSmart ----- Frank Kabel wrote: ----- Hi try =SUMPRODUCT(--(MONTH($O$11:$BN$11)=MONTH(EK$11))*ISNUMBER($O20:$ BN20))* CZ20 -- Regards Frank Kabel Frankfurt, Germany BeSmart wrote: Thanks Muchly Frank That worked well if the user enters 12 but that number could be any number between 1-31 (ie any date in a month) Any suggestions? BeSmart.. ----- Frank Kabel wrote: ----- Hi try =SUMPRODUCT(--(MONTH($O$11:$BN$11)=MONTH(EK$11))*($O20:$BN20=12) )*CZ20 -- Regards Frank Kabel Frankfurt, Germany BeSmart wrote: I'm having problems with a schedule where if users nominate "Job start" on a row, they need to enter dates instead of general numbers into the schedule eg they might enter a 12 in the W/C 7/3/04 column to identify the 12th March. I need the formula to take 12 and count it as 1 then multiply it by the cost in CZ20 ($1000). When "Job Start" or dates are not entered the following formula works fine: =SUMPRODUCT(--(MONTH($O$11:$BN$11)=MONTH(EK$11)),$O20:$BN20)*CZ2 0) When dates are entered the formula does not work - it needs to count the active cells instead of summing them. I tried to add the COUNTA function to the formula but it did not like it. I think need a different way to calculate the data? Two solutions were suggested but neither is working: 1. Exclude the O20:BN20 range. But then the formula only calculates against the number of weeks in the month instead of the activity resulting in $4000 for the month of March?? =IF(A20="Job start",SUMPRODUCT(--(MONTH($O$11:$BN$11)=MONTH(EK$11)))*CZ20,SUMPRODUC T (--(MONTH($O$11:$BN$11)=MONTH(EK$11)),$O20:$BN20)*CZ2 0)) 2. To divide the month result by the month (???) to get a count, this definitely didn't work - again the current row of activity is not included, plus the month number (eg 3 for March) has no relationship with the changing number of insertions entered into (O20:BN20) (ie neither 12 or 1 in W/C 7/3/04 works). The result here was $1333??? =IF(A20="Job start",SUMPRODUCT(--(MONTH($O$11:$BN$11)=MONTH(EK$11)))/MONTH(EK$11)*CZ 20,SUMPRODUCT(--(MONTH($O$11:$BN$11)=MONTH(EK$11)),$O20:$BN20)*CZ2 0)) If anyone can advise on what formula I should use here I would greatly appreciate it. TFYH |
#8
|
|||
|
|||
Converting a SUMPRODUCT formula to COUNTA active...
Watch your "coercers"...
By prefernce... either =SUMPRODUCT(--(MONTH($O$11:$BN$11)=MONTH(EK$11)),--ISNUMBER($O20:$BN20))*CZ2 0 or =SUMPRODUCT((MONTH($O$11:$BN$11)=MONTH(EK$11))*ISN UMBER($O20:$BN20))*CZ20 "Frank Kabel" wrote in message ... Hi try =SUMPRODUCT(--(MONTH($O$11:$BN$11)=MONTH(EK$11))*ISNUMBER($O20:$ BN20))* CZ20 -- Regards Frank Kabel Frankfurt, Germany BeSmart wrote: Thanks Muchly Frank That worked well if the user enters 12 but that number could be any number between 1-31 (ie any date in a month) Any suggestions? BeSmart.. ----- Frank Kabel wrote: ----- Hi try =SUMPRODUCT(--(MONTH($O$11:$BN$11)=MONTH(EK$11))*($O20:$BN20=12) )*CZ20 -- Regards Frank Kabel Frankfurt, Germany BeSmart wrote: I'm having problems with a schedule where if users nominate "Job start" on a row, they need to enter dates instead of general numbers into the schedule eg they might enter a 12 in the W/C 7/3/04 column to identify the 12th March. I need the formula to take 12 and count it as 1 then multiply it by the cost in CZ20 ($1000). When "Job Start" or dates are not entered the following formula works fine: =SUMPRODUCT(--(MONTH($O$11:$BN$11)=MONTH(EK$11)),$O20:$BN20)*CZ2 0) When dates are entered the formula does not work - it needs to count the active cells instead of summing them. I tried to add the COUNTA function to the formula but it did not like it. I think need a different way to calculate the data? Two solutions were suggested but neither is working: 1. Exclude the O20:BN20 range. But then the formula only calculates against the number of weeks in the month instead of the activity resulting in $4000 for the month of March?? =IF(A20="Job start",SUMPRODUCT(--(MONTH($O$11:$BN$11)=MONTH(EK$11)))*CZ20,SUMPRODUC T (--(MONTH($O$11:$BN$11)=MONTH(EK$11)),$O20:$BN20)*CZ2 0)) 2. To divide the month result by the month (???) to get a count, this definitely didn't work - again the current row of activity is not included, plus the month number (eg 3 for March) has no relationship with the changing number of insertions entered into (O20:BN20) (ie neither 12 or 1 in W/C 7/3/04 works). The result here was $1333??? =IF(A20="Job start",SUMPRODUCT(--(MONTH($O$11:$BN$11)=MONTH(EK$11)))/MONTH(EK$11)*CZ 20,SUMPRODUCT(--(MONTH($O$11:$BN$11)=MONTH(EK$11)),$O20:$BN20)*CZ2 0)) If anyone can advise on what formula I should use here I would greatly appreciate it. TFYH |
#9
|
|||
|
|||
Converting a SUMPRODUCT formula to COUNTA active...
Aladin Akyurek wrote:
Watch your "coercers"... Thanks :-) %$%&% copy&paste Frank |
Thread Tools | |
Display Modes | |
|
|