# SUMPRODUCT((ISNUMBER(SEARCH() function

1. ## SUMPRODUCT((ISNUMBER(SEARCH() function

Never mind - I got it!!

2. Hi,

Does this work for you

=SUMPRODUCT(--(ISNUMBER(SEARCH("AM",C5:C453)))*(--(K5:K453={"complete","Not Done"})))

VBA Noob

3. I'll try it 'cause my solution didn't work as planned...

4. 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"))

5. Works for me

See attached

VBA Noob

6. 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?

7. Can you post your example ??

VBA Noob

8. don't know what I did wrong the first time, but works now - thanks...

for my knowledge, why was mine pulling am/pm weird?

9. here's my sheet

(i think)

10. So what's the expected results and in what cells

VBA Noob

11. trying to get a count of the jobs with complete and not done.

am and pm results in different cells (K1 and K3)

12. 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

13. all good now - thanks for all your help and patience.

14. No Problem

Thanks for the feedback

VBA Noob

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1