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

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

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

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

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

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

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

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

Aladin Akyurek wrote:
Watch your "coercers"...


Thanks :-)
%$%&% copy&paste

Frank