SUMPRODUCT(--ISNUMBER(FIND("AM",C5:160))*(k5:k160="")
 

I use this first one to find "AM" or "PM" in a range:
SUMPRODUCT(--ISNUMBER(FIND("AM",C5:160))



This one I use to to find "complete" or "not done", then count the job
types i.e. the "RC", "NC", etc.
SUMPRODUCT((c5:c160={"RC","NC","RS","dd","d1nc","s dnc","sdddnc"})*(K5:K160=
"complete"))


I need to do the function of the second - look for particular cell
content - then look for the AM/PM part.

I need to look for a BLANK cell in the K5:K160 range, then sum the
AM/PM's in C5:C150. The cell containing AM/PM also contains the date,
that is why I use the first formula.



the thread title is just one of the ones I've tried that did not work.


Anyone help a feller out?


--
redneck joe


------------------------------------------------------------------------
redneck joe's Profile: http://www.excelforum.com/member.php...o&userid=32570
View this thread: http://www.excelforum.com/showthread...hreadid=570547

SUMPRODUCT(--ISNUMBER(FIND("AM",C5:160))*(k5:k160="")
 

Is it not just

=SUMPRODUCT(--(K5:K160=""),MOD(C5:C160,1))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"redneck joe"
wrote in message
...

I use this first one to find "AM" or "PM" in a range:
SUMPRODUCT(--ISNUMBER(FIND("AM",C5:160))



This one I use to to find "complete" or "not done", then count the job
types i.e. the "RC", "NC", etc.

SUMPRODUCT((c5:c160={"RC","NC","RS","dd","d1nc","s dnc","sdddnc"})*(K5:K160=
"complete"))


I need to do the function of the second - look for particular cell
content - then look for the AM/PM part.

I need to look for a BLANK cell in the K5:K160 range, then sum the
AM/PM's in C5:C150. The cell containing AM/PM also contains the date,
that is why I use the first formula.



the thread title is just one of the ones I've tried that did not work.


Anyone help a feller out?


--
redneck joe


------------------------------------------------------------------------
redneck joe's Profile:
http://www.excelforum.com/member.php...o&userid=32570
View this thread: http://www.excelforum.com/showthread...hreadid=570547

SUMPRODUCT(--ISNUMBER(FIND("AM",C5:160))*(k5:k160="")
 

Bob Phillips Wrote:
Is it not just

=SUMPRODUCT(--(K5:K160=""),MOD(C5:C160,1))

--
HTH

Bob Phillips




No it returned - #value.

I'm not sure what MOD is. That's new to me...


Any links to study up on that one?


--
redneck joe


------------------------------------------------------------------------
redneck joe's Profile: http://www.excelforum.com/member.php...o&userid=32570
View this thread: http://www.excelforum.com/showthread...hreadid=570547

SUMPRODUCT(--ISNUMBER(FIND("AM",C5:160))*(k5:k160="")
 

Mod just takes the fractional part. As I read your post, C5:C160 was date
and time and you just wanted the time, so I MOD it with 1 to get the time,
and add these where K is blank.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"redneck joe"
wrote in message
...

Bob Phillips Wrote:
Is it not just

=SUMPRODUCT(--(K5:K160=""),MOD(C5:C160,1))

--
HTH

Bob Phillips




No it returned - #value.

I'm not sure what MOD is. That's new to me...


Any links to study up on that one?


--
redneck joe


------------------------------------------------------------------------
redneck joe's Profile:
http://www.excelforum.com/member.php...o&userid=32570
View this thread: http://www.excelforum.com/showthread...hreadid=570547

SUMPRODUCT(--ISNUMBER(FIND("AM",C5:160))*(k5:k160="")
 

Bob Phillips Wrote:
Mod just takes the fractional part. As I read your post, C5:C160 was
date
and time and you just wanted the time, so I MOD it with 1 to get the
time,
and add these where K is blank.

--
HTH

Bob Phillips




thanks Bob - I'll play with that and see what I can come up with.

j


--
redneck joe


------------------------------------------------------------------------
redneck joe's Profile: http://www.excelforum.com/member.php...o&userid=32570
View this thread: http://www.excelforum.com/showthread...hreadid=570547

SUMPRODUCT(--ISNUMBER(FIND("AM",C5:160))*(k5:k160="")
 

the winning formula:

=SUMPRODUCT((ISNUMBER(SEARCH("AM",C5:C453)))*(K5:K 453=""))





I was trying ((--ISNUMBER(FIND("AM",C5:C453)))*(K5:K453=""))



Anyway, all is good until the size of this file and the volume of users
sharing the workkbook just kills the whole system.


I guess SEARCH works differently than FIND.







Bob Phillips Wrote:
Mod just takes the fractional part. As I read your post, C5:C160 was
date
and time and you just wanted the time, so I MOD it with 1 to get the
time,
and add these where K is blank.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"redneck joe"

wrote in message
...

Bob Phillips Wrote:
Is it not just

=SUMPRODUCT(--(K5:K160=""),MOD(C5:C160,1))

--
HTH

Bob Phillips




No it returned - #value.

I'm not sure what MOD is. That's new to me...


Any links to study up on that one?


--
redneck joe



------------------------------------------------------------------------
redneck joe's Profile:
http://www.excelforum.com/member.php...o&userid=32570
View this thread:
http://www.excelforum.com/showthread...hreadid=570547



--
redneck joe


------------------------------------------------------------------------
redneck joe's Profile: http://www.excelforum.com/member.php...o&userid=32570
View this thread: http://www.excelforum.com/showthread...hreadid=570547