Wildcard character in an "If" statement?
Tiredazdaddy,
Excel has explanations on wildcard functions in text strings in the help
files. As I was looking at your post, I realized that my post was close to
two years ago now.
My particular post had to do with looking to have what I called a dual duty
test. I.e., if I had one string, then I wanted it to perform a particular
function. If it had another string, it'd perform another. While I solved my
particular problem, I haven't used it but perhaps once or twice since then.
The way your string is configured, I think what you want is not a sum, with
an if equation, but one that's called Sumproduct.
It allows for up to 30 criteria (in pre-Excel2007), and up to 255 criteria
in Excel2007.
I also use that to perform count functions where I have more than one
criteria.
=SUM(IF(('Input Form'!C$4:C$549=$G$3)*('Input Form'!D$4:D$549=$H$3)*('Input
Form'!R$4:R$549<=$A126)*(NOT(ISBLANK('Input Form'!R$4:R$549))),1,0))
would become
=sumproduct(('Input Form'!C$4:C$549=$G$3)*('Input
Form'!D$4:D$549=$H$3)*('Input Form'!R$4:R$549<=$A126)*('Input
Form'!R$4:R$549))
each dataset within paren's is an array for the first three elements in your
equation.
The last set appears to return a boolean, or true/false response.
=SUM(IF(('Input Form'!C$4:C$549=$G$3)*('Input Form'!D$4:D$549=$H$3)*('Input
Form'!R$4:R$549<=$A126)*(NOT(ISBLANK('Input Form'!R$4:R$549))),1,0))
What I'm seeing here is the following-- and I could be missing something, so
if I am, please forgive me.
You're looking to compare a specific cell against a range to find that
element- $G$3
AND you want to compare $H$3 to a second range.
AND you want to compare $A126 to be = the final range.
WHERE the range R4:$549 is not blank.
where all these properties are true, you want to return true-1, or if not
true, you return a false- 0.
For all of your true values, you want them summed.
Is my understanding correct?
If I'm understanding what you're asking, the sumproduct would work. But, as
you mentioned, it only works for exact matches. I haven't figured out
Sumproduct looks at all occurences within the range, that match G3, H3, and
<=A126. Each of those return either true or false-- 1 for true, 0 for false.
The last range is your sum range. R6:R549.
So, the response will look something like-
1*1*1*1*value = value.
0*1*1*1*value = 0-- all variations with 0 will return 0.
It then adds all values, and gives the total of all the values.
One of the problems that I've found with linking to other worksheets-- in
your case, Input Form-- is that the cells can have different data types. When
this occurs, it will never return true, or 1 for those arrays.
I've found that with my data type troubles, there are data-type converters.
Your equation would then look like:
=sumproduct(('Input Form'!C$4:C$549&""=$G$3&"")*('Input
Form'!D$4:D$549&""=$H$3&"")*('Input Form'!R$4:R$549<=$A126)*('Input
Form'!R$4:R$549))
Note that the convertors are an ampersand with two double quotes &"".
where, the first two arrays are some text, or general datatype. Since you're
using <= with the 3rd array, I'll assume you have a numeric datatype there.
and the last array is your sum range- also numeric.
you can read more on sumproduct in the excel help file as well.
My next question to you would be-- did you look at my answer from David B?
HTH.
"tiredazdaddy" wrote:
Hi Steve,
I have something similar as I saw here also but is a little more complex. Do
you have any idea on how I can use wildcards to do something similar but with
it nested in with other variables?
I am trying to figure out how to search for a specific alphanumeric value
from a cell in a character string in another cell.
For instance, the below works fine if I only want to find an exact match for
the values within column D for that in cell H3, but I do not know how to
find the same value from H3 if the values in column D contain a match
mixed in a character string.
Any help would be greatly appreciated!
=SUM(IF(('Input Form'!C$4:C$549=$G$3)*('Input Form'!D$4:D$549=$H$3)*('Input
Form'!R$4:R$549<=$A126)*(NOT(ISBLANK('Input Form'!R$4:R$549))),1,0))
"T. Valko" wrote:
Here's one way:
=IF(COUNTIF(A1,"*stringname*"),B1*0.5,B1*0.75)
Or
C1 = stringname
=IF(COUNTIF(A1,"*"&C1&"*"),B1*0.5,B1*0.75)
Biff
SteveDB1" wrote in message
...
I want to do what seems to be a similar IF function.
Here is my version.
=IF(a1="*stringname*",b1*1/2,b1*3/4)
My goal is to look in a cell which contains a phrase. The phrase itself
varies {the cell contents could be a name of a person, with (word)
following
it}, but the particular component that I'm seeking either shows up as
(word),
or as (word1). Eg., cell contents being within the dbl quote marks: "Dave
Johnson (word)", or "Danny Thomas (word1)"
Where "word" could be anything.
I've tried already, and it <always returns a false value-- b1*3/4.
1- can I do this?
2- what would I need to do in order to make it work?
I've also tried the tilda, and question mark. Neither of those are
working.
If however, I just have it look in a cell with a single character, the
equation works fine [=if(a1="c",b1*1/2,b1*3/4)]. I'd assume that it'd work
well too if I had just a single word in the cell, as opposed to a number
of
them. For some reason I just can't get it to work with longer elements,
where
I want to locate a single word within a string of 5 or six words.
If I'm unable to do this, what variation would I need to accomplish this?
Thank you.
|