sumproduct question

[SteveDB1]

Hi all.

I'm doing a variation on the sumproduct equation that I've had working for a
while, and while it's not returning any errors, I am getting a null answer,
which I know to not be true-- by having looked.

The general form of it that I'm using is:
sumproduct((range1=cella1)*(range2&""=cella2&"")*(range3))

Per previous discussions I've found that the &"" acts as a data type
converter.

Today I'd like to attempt to expand that to 4 arrays, from 3.

My form is:

sumproduct((range1=cella1)*(range2&""=cella2&"")*(range4&""=any-arbitrary-#)*(range3))

The addition of the any-arbirtrary-# to the equation is what's causing the
null answer, or 0 to be returned.

Since I'd really like to have this be generic enough to adapt to different
worksheets, I need to have that any-arbitrary-# become a statement that will
look for a specific number within a text string.

E.g.

(range4&""=string(Somebody's data 3632))

and I want to obtain the 3632 out of the string to be compared against the
range4&"". If it returns a true, then it's valid.

I have looked at search(), as well as find(), and by appearances it only
returns the value, not a true, or false. Thus still giving me a null, or 0
answer.

Any ideas as to how I could do this?

Thank you for your time.

 

[Pete_UK]

ISNUMBER(SEARCH( ... ) ) (or FIND) will return True or False, so
perhaps you can make use of this.

Hope this helps.

Pete