Never mind - I got it!!
Never mind - I got it!!
Last edited by redneck joe; 12-08-2006 at 03:53 PM.
Better to be roughly right than exactly wrong, unless you are using Excel.
Hi,
Does this work for you
=SUMPRODUCT(--(ISNUMBER(SEARCH("AM",C5:C453)))*(--(K5:K453={"complete","Not Done"})))
VBA Noob
_________________________________________
Credo Elvem ipsum etian vivere
_________________________________________
A message for cross posters
Please remember to wrap code.
Forum Rules
Please add to your signature if you found this link helpful. Excel links !!!
I'll try it 'cause my solution didn't work as planned...
no sir - that is returning a 0.
Here's what I had that (sort of) worked, however it is not properly differentiating between the AM and the PM that I am searching for...
SUMPRODUCT((ISNUMBER(SEARCH("pm",C6:C454)))*(K6:K454="contact")+(K6:K454="complete")+(K6:K454="not done"))
Works for me
See attached
VBA Noob
Been playing around a bit more -
I have the same formula in different cells, the only change being the AM and the PM.
All works as planned if I enter in any of the validations if I stay with all PM jobs (or AM).
When I have, say, 4 completed PM (only) jobs- my PM formula works just fine. When I then hit completed on an AM job, it calculates on the AM formula fine, but also adds another to the PM formula.
so, it would then display 5 completed PM jobs and 1 completed AM job, total of six even though I only have five validated.
Am I making any sense?
Can you post your example ??
VBA Noob
don't know what I did wrong the first time, but works now - thanks...
for my knowledge, why was mine pulling am/pm weird?
here's my sheet
(i think)
So what's the expected results and in what cells
VBA Noob
trying to get a count of the jobs with complete and not done.
am and pm results in different cells (K1 and K3)
For the info you have I get 1 and 1 using
=SUMPRODUCT(--(ISNUMBER(SEARCH("AM",C6:C453)))*(--(K6:K453={"complete","Not Done","contact"})))
=SUMPRODUCT(--(ISNUMBER(SEARCH("PM",C6:C453)))*(--(K6:K453={"complete","Not Done","contact"})))
VBA Noob
all good now - thanks for all your help and patience.
No Problem
Thanks for the feedback
VBA Noob
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks