Multiple Count IFs - Page 2
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 13 of 13

Thread: Multiple Count IFs

  1. #11
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,445
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Quote Originally Posted by jereece
    =SUMPRODUCT((A2:A8=1)*(B2:B8="pass"))

    This seems to work for me. One last question, is there a way to get this to work with wild cards (i.e. "*pass*")? I have one database I want to use this with and the entries are not 100% consistent. If I could use a wild card, that would eliminate my problem.

    I tried for example =SUMPRODUCT((A2:A8=1)*(B2:B8,*pass*)) but keep getting error messages.

    Can anyone help?

    Thanks,
    Jim
    Try,

    =SUMPRODUCT(--(ISNUMBER(SEARCH("*pass*",B1:B10))),--(A1:A10=1))

    Sumproduct in itself does not accept wildcards

  2. #12
    New Member
    Join Date
    Mar 2002
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have never seen the search term first ("Data!*pass*",B1:B10), is that correct?

    Also, if the data is on a different tab, would it be

    =SUMPRODUCT(--(ISNUMBER(SEARCH("Data!*pass*",B1:B10))),--(Data!A1:A10=1))

  3. #13
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,799
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

      
    Quote Originally Posted by jereece
    I have never seen the search term first ("Data!*pass*",B1:B10), is that correct?

    Also, if the data is on a different tab, would it be

    =SUMPRODUCT(--(ISNUMBER(SEARCH("Data!*pass*",B1:B10))),--(Data!A1:A10=1))
    No, that's not correct...

    =SUMPRODUCT(--ISNUMBER(SEARCH("pass",Data!B1:B10)),--(Data!A1:A10=1))

    You can drop *'s around the word pass because SEARCH is capable of locating the search string anywhere in the target string.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com