Consulting

Results 1 to 9 of 9

Thread: Solved: a job for SP?

  1. #1
    Knowledge Base Approver VBAX Wizard
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,076

    Solved: a job for SP?

    The shaded area (C7:C9) is a named range called myRange.
    2011-10-22_004651.jpg

    I would like a count of names and/or a sum of column B of those people whose names do not contain any of the names in myRange.
    This will be part, hopefully of a larger sumproduct formula where there are more conditions to satisfy.

    (File attached in next message)
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  2. #2
    Knowledge Base Approver VBAX Wizard
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,076
    Attached file for message above.
    Attached Files Attached Files
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  3. #3
    Knowledge Base Approver VBAX Guru GTO's Avatar
    Joined
    Sep 2008
    Posts
    3,364
    Location
    Hi Pascal,

    As you no doubt know, I'm not stellar at formulas, but I think this actually works.

    Where MyRange refers to: =Sheet1!$C$7:$C$9
    Formula: =SUMPRODUCT(--ISERROR(MATCH(A1:A5,MyRange,0)),B1:B5)

    I would add that in C7:C9, I entered the subjects' first and last names, as I guessed that the omitting of last names was unintentional? (At home currently, so I went by the picture, as I cannot see/open 2007+)

    Hope this helps?

    Mark
    Attached Files Attached Files

  4. #4
    Knowledge Base Approver VBAX Wizard
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,076
    Thanks Mark, this does work with your:
    Quote Originally Posted by GTO
    in C7:C9, I entered the subjects' first and last names, as I guessed that the omitting of last names was unintentional?
    however, as you're probably guessing already, the last names were not unintentionally omitted.

    I've got a little further with the attached, though this may be a misdirection, where in E1 I've array-entered:
    [vba]=OR(ISNUMBER(SEARCH("*" & MyRange,A1)))[/vba](and I should have added NOT around the formula) and copied down, but haven't managed to put this in a single cell with or without SUMPRODUCT.
    Attached Files Attached Files
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  5. #5
    Knowledge Base Approver VBAX Guru GTO's Avatar
    Joined
    Sep 2008
    Posts
    3,364
    Location
    Quote Originally Posted by p45cal
    Thanks Mark, this does work with your:however, as you're probably guessing already, the last names were not unintentionally omitted...
    Nope, afraid I was a little slow on the uptake. That, or I just didn't want this to be the case (cuz it seems a lot dang harder! ) LOL.

    Okay, maybe not ideal, but how about:
    [vba]=SUMPRODUCT(--ISERROR(MATCH(LEFT(A1:A5,SEARCH(" ",A1:A5)-1),MyRange,0)),B1:B5)[/vba]

    Mark

  6. #6
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,598
    Location
    I believe that:
    =SUM(B1:B5)-SUMPRODUCT((ISNUMBER(SEARCH(TRANSPOSE(myRange),A1:A5)))*B1:B5)
    array-entered will do that.
    Be as you wish to seem

  7. #7
    Knowledge Base Approver VBAX Wizard
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,076
    Thanks both to Mark and Aflatoon. While Mark's works perfectly for the problem stated, Aflatoon's adds the flexibility of the strings in myRange not having to be just the first name/word. I will spend some time working out the logic, trying to commit it to memory and then incorporating it into a larger formula.

    Thank you again!
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  8. #8
    VBAX Expert shrivallabha's Avatar
    Joined
    Jan 2010
    Location
    Mumbai
    Posts
    750
    Location
    Probably little late on this.
    To COUNT instances:
    =COUNT(FIND(TRANSPOSE(myRange),A1:A5,1))
    Without SUMPRODUCT formula:
    =SUM(IF(ISERROR(MATCH(LEFT(A1:A5,FIND(" ",A1:A5,1)-1),myRange,0)),B1:B5))
    Both are ARRAY formulas.
    Regards,
    --------------------------------------------------------------------------------------------------------
    Shrivallabha
    --------------------------------------------------------------------------------------------------------
    Using Excel 2016 in Home / 2010 in Office
    --------------------------------------------------------------------------------------------------------

  9. #9
    Knowledge Base Approver VBAX Wizard
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,076
    Quote Originally Posted by shrivallabha
    Probably little late on this.
    To COUNT instances:
    =COUNT(FIND(TRANSPOSE(myRange),A1:A5,1))
    Without SUMPRODUCT formula:
    =SUM(IF(ISERROR(MATCH(LEFT(A1:A5,FIND(" ",A1:A5,1)-1),myRange,0)),B1:B5))
    Both are ARRAY formulas.
    I can use the first, thank you, with a little adjustment for a count of those not containing the string:
    =ROWS(A1:A5)-COUNT(FIND(TRANSPOSE(myRange),A1:A5,1))

    The other gives the same result as Mark's in msg#5, so comparing with Aflatoon's, it's not as flexible (only matches first word, not any string), and it needs array-entering, but does show that the answer to the subject of this thread could be "Not necessarily" !
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

Posting Permissions

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