Converting a SUMPRODUCT formula to COUNTA active...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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)*CZ20

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,SUMPRODUCT(--(MONTH($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,SUMPRODUCT(--(MONTH($O$11:$BN$11)=MONTH(EK$11)),$O20:$BN20)*CZ20)

If anyone can advise on what formula I should use here I would greatly appreciate it
TFYH
 
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
(e-mail address removed)
BeSmart said:
I'm having problems with a schedule where if users nominate "Job start" on
Click to expand...
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)*CZ20)

When dates are entered the formula does not work - it needs to count the
Click to expand...
active cells instead of summing them.
I tried to add the COUNTA function to the formula but it did not like it.
Click to expand...
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
Click to expand...
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,SUMPRODUCT(--(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
Click to expand...
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???
 
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
(e-mail address removed)
BeSmart said:
I'm having problems with a schedule where if users nominate "Job start" on
Click to expand...
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.
fine:
Click to expand...
active cells instead of summing them.
I tried to add the COUNTA function to the formula but it did not like it.
Click to expand...
I think need a different way to calculate the data?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,SUMPRODUCT(--(M
ONTH($O$11:$BN$11)=MONTH(EK$11)),$O20:$BN20)*CZ20))
Click to expand...
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???
 
Thanks Muchly Fran
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: ----

H
tr
=SUMPRODUCT(--(MONTH($O$11:$BN$11)=MONTH(EK$11))*($O20:$BN20=12))*CZ2

-
Regard
Frank Kabe
Frankfurt, German


BeSmart wrote
 
BRILLIANT!!!! Thanks heaps Frank - that's had me stumped all day!!! (I'm in NZ so your night is my day
Cheer
BeSmar

----- Frank Kabel wrote: ----

H
tr
=SUMPRODUCT(--(MONTH($O$11:$BN$11)=MONTH(EK$11))*ISNUMBER($O20:$BN20))
CZ2


-
Regard
Frank Kabe
Frankfurt, German


BeSmart wrote
 
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))*ISNUMBER($O20:$BN20))*CZ20
 
Back
Top