A job for SP?

Printable View

10-21-2011, 04:51 PM
p45cal

1 Attachment(s)

A job for SP?

The shaded area (C7:C9) is a named range called myRange.
Attachment 6746

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)
10-21-2011, 04:52 PM
p45cal

1 Attachment(s)

Attached file for message above.
10-21-2011, 07:35 PM
GTO

1 Attachment(s)

Hi Pascal, :hi:

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

Where MyRange refers to:

Code:

=Sheet1!$C$7:$C$9

Formula:

Code:

=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
10-22-2011, 12:42 AM
p45cal

1 Attachment(s)

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:

Code:

=OR(ISNUMBER(SEARCH("*" & MyRange,A1)))

(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.
10-22-2011, 08:14 AM
GTO

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!:eek: ) LOL.

Okay, maybe not ideal, but how about:

Code:

=SUMPRODUCT(--ISERROR(MATCH(LEFT(A1:A5,SEARCH(" ",A1:A5)-1),MyRange,0)),B1:B5)

Mark
10-24-2011, 09:15 AM
Aflatoon

I believe that:

Code:

=SUM(B1:B5)-SUMPRODUCT((ISNUMBER(SEARCH(TRANSPOSE(myRange),A1:A5)))*B1:B5)

array-entered will do that.
10-24-2011, 10:40 AM
p45cal

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!
10-27-2011, 03:18 AM
shrivallabha

Probably little late on this.
To COUNT instances:

Code:

=COUNT(FIND(TRANSPOSE(myRange),A1:A5,1))

Without SUMPRODUCT formula:

Code:

=SUM(IF(ISERROR(MATCH(LEFT(A1:A5,FIND(" ",A1:A5,1)-1),myRange,0)),B1:B5))

Both are ARRAY formulas.
10-27-2011, 03:56 AM
p45cal

Quote:

Originally Posted by shrivallabha

Probably little late on this.
To COUNT instances:

Code:

=COUNT(FIND(TRANSPOSE(myRange),A1:A5,1))

Without SUMPRODUCT formula:

Code:

=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:

Code:

=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" !