sumproduct question
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. |
sumproduct question
ISNUMBER(SEARCH( ... ) ) (or FIND) will return True or False, so
perhaps you can make use of this. Hope this helps. Pete On Sep 21, 7:36 pm, SteveDB1 wrote: 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. |
All times are GMT +1. The time now is 08:02 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com