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","sdnc","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?

Originally Posted by Bob Phillips

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?



For the first part of the formula, I am trying to look in the same column as the "complete" but now for the blank cell. If it matters this column has validations for "complete" and "not done"

The second part of the function, I'm no longer trying to find the job type ("RS", "NC", etc) any longer, rather I am searching a new column for the "AM". This cell would contain "08/11/2006 AM"

Originally Posted by 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

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

j

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 users sharing the workkbook just kills the whole system.


I guess SEARCH works differently than FIND.

Originally Posted by 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
news:[email protected]...
>
> 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
>