A job for SP?

[p45cal]

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]

Attached file for message above.

[GTO]

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

[p45cal]

Thanks Mark, this does work with your:

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:

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

[GTO]

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:

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

Mark

[Aflatoon]

I believe that:

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

array-entered will do that.

[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!

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

[p45cal]

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