Isnumber(find( but not "a",

[Long Nose]

Hi all,

Excel 2010

I am trying to use a wild card in a Sumproduct formula. It is working to find the "a" in a column and returning the expected value, but what I really want to do is exclude the "a" cells. Any ideas?

formula

=SUMPRODUCT(--(SUB580AAL_SubsSalesBySource.xlsx!$A$1:$A$3000="CM"),--(SUB580AAL_SubsSalesBySource.xlsx!$A$1:$A$3000<>""),--ISNUMBER(FIND("A",SUB580AAL_SubsSalesBySource.xlsx!$C$1:$C$3000)),--(SUB580AAL_SubsSalesBySource.xlsx!$B$1:$B$3000=TYPE_S),--(SUB580AAL_SubsSalesBySource.xlsx!$E$1:$E$3000=Curr_Mo),--(SUB580AAL_SubsSalesBySource.xlsx!$F$1:$F$3000=Curr_YR),SUB580AAL_SubsSalesBySource.xlsx!$H$1:$H$3000)

This is the part I need to find a fix for. I need to exclude the "a".
--ISNUMBER(FIND("A",SUB580AAL_SubsSalesBySource.xlsx!$C$1:$C$3000)),

 

[Oeldere]

I'm not sure, but you can give it a shot.

--ISNUMBER(FIND([COLOR=red]<>[/COLOR]"A",SUB580AAL_SubsSalesBySource.xlsx!$C$1:$C$3000)),

<!-- / message --><!-- edit note -->

 

[Long Nose]

That <> was my first thought too, but excel didn't like it and valued out.

Right now I could use the find "A" which is what I don't want, then I could write the formula without the wild card and get all the values including those without the "A" and then subtract the two formulas resulting in the correct number, but this seems unnecessarily complicated.

 

[T. Valko]

That <> was my first thought too, but excel didn't like it and valued out.

Right now I could use the find "A" which is what I don't want, then I could write the formula without the wild card and get all the values including those without the "A" and then subtract the two formulas resulting in the correct number, but this seems unnecessarily complicated.

Just at a glance...

Replace ISNUMBER with ISERROR.

 

[Dryver14]

the only real way you are going to be able to do this is by putting the find in an if statement before running the formula. I don't think there is a way to tell find to find anything but

 

[Long Nose]

Just at a glance...

Replace ISNUMBER with ISERROR.
__________________
.
Biff

That does work, but it really has me puzzled on why.

Thank you.

 

[barry houdini]

You are really just reversing what you currently have - FIND returns either a number or an error, so ISERROR, as per Biff's suggestion, is the opposite of ISNUMBER, another way is to change this

--ISNUMBER(FIND("A",SUB580AAL_SubsSalesBySource.xlsx!$C$1:$C$3000))

to this

1-ISNUMBER(FIND("A",SUB580AAL_SubsSalesBySource.xlsx!$C$1:$C$3000))

Note: I just replaced the first "-" with a 1

 

[Dryver14]

aha,

good one essencially converting a true return to a false

 

[Long Nose]

You are really just reversing what you currently have - FIND returns either a number or an error, so ISERROR, as per Biff's suggestion, is the opposite of ISNUMBER, another way is to change this

--ISNUMBER(FIND("A",SUB580AAL_SubsSalesBySource.xlsx!$C$1:$C$3000))

to this

1-ISNUMBER(FIND("A",SUB580AAL_SubsSalesBySource.xlsx!$C$1:$C$3000))

Note: I just replaced the first "-" with a 1

Thank you Barry. I learned something new today.

 

[T. Valko]

That does work, but it really has me puzzled on why.

I see Barry explained it for you.

Thank you.

You're welcome!