+ Reply to Thread
Results 1 to 14 of 14

SUMPRODUCT((ISNUMBER(SEARCH() function

  1. #1
    Registered User
    Join Date
    03-17-2006
    MS-Off Ver
    2007
    Posts
    93

    SUMPRODUCT((ISNUMBER(SEARCH() function

    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.

  2. #2
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    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 !!!

  3. #3
    Registered User
    Join Date
    03-17-2006
    MS-Off Ver
    2007
    Posts
    93
    I'll try it 'cause my solution didn't work as planned...

  4. #4
    Registered User
    Join Date
    03-17-2006
    MS-Off Ver
    2007
    Posts
    93
    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. #5
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Works for me

    See attached

    VBA Noob
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    03-17-2006
    MS-Off Ver
    2007
    Posts
    93
    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. #7
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Can you post your example ??

    VBA Noob

  8. #8
    Registered User
    Join Date
    03-17-2006
    MS-Off Ver
    2007
    Posts
    93
    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. #9
    Registered User
    Join Date
    03-17-2006
    MS-Off Ver
    2007
    Posts
    93
    here's my sheet


    (i think)
    Attached Files Attached Files

  10. #10
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    So what's the expected results and in what cells

    VBA Noob

  11. #11
    Registered User
    Join Date
    03-17-2006
    MS-Off Ver
    2007
    Posts
    93
    trying to get a count of the jobs with complete and not done.

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

  12. #12
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    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
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    03-17-2006
    MS-Off Ver
    2007
    Posts
    93
    all good now - thanks for all your help and patience.

  14. #14
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    No Problem

    Thanks for the feedback

    VBA Noob

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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