View Full Version : Solved: a job for SP?

p45cal

10-21-2011, 04:51 PM

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

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)

p45cal

10-21-2011, 04:52 PM

Attached file for message above.

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

10-22-2011, 12:42 AM

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.

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:

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

Mark

Aflatoon

10-24-2011, 09:15 AM

I believe that:

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

array-entered will do that.

p45cal

10-24-2011, 10:40 AM

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

10-27-2011, 03:18 AM

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

10-27-2011, 03:56 AM

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

Powered by vBulletin® Version 4.2.5 Copyright © 2021 vBulletin Solutions Inc. All rights reserved.