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

[redneck joe]

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 jo
types i.e. the "RC", "NC", etc.
SUMPRODUCT((c5:c160={"RC","NC","RS","dd","d1nc","sdnc","sdddnc"})*(K5:K160
"complete"))


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

I need to look for a BLANK cell in the K5:K160 range, then sum th
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

 

[Bob Phillips]

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" <[email protected]>
wrote in message
news:[email protected]...

 

[redneck joe]

Is it not just

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

No it returned - #value.

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


Any links to study up on that one

 

[Bob Phillips]

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" <[email protected]>
wrote in message

 

[redneck joe]

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.

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

j

 

[redneck joe]

the winning formula:

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





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



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


I guess SEARCH works differently than FIND.