Wildcards with Array Formulae

[Jamesmoore]

Hi,
I'm looking for some help with a formula I'm using to pull statistic
out of a client spreadsheet. The formula uses the SUMIF command to loo
at one column, check for a text value then look at another colum
within the same row and check for another text value. It returns
value of one if both are true and a value of 0 if one or none are tru
then keeps a total. It's a pretty standard array formula, but I need i
to use wildcards because the second column can contain value A, value
or value AB. I want the formula to pick up all instances where value
appears, including where it appears within AB. The formula I have use
is as follows -

=SUM(IF((A1:A1000="string1")*(F1:F1000="A"),1,0))

This works to an extent, but doesn't pick up instances where value
appears in a cell containing AB as it is looking for only A. If I add
wildcard "*A*" the formula doesn't work any more... Does anybody hav
any suggestions? Any help would be very much appreciated...!!! Thank
in advance.

Jame

 

[Paul]

Hi,
I'm looking for some help with a formula I'm using to pull statistics
out of a client spreadsheet. The formula uses the SUMIF command to look
at one column, check for a text value then look at another column
within the same row and check for another text value. It returns a
value of one if both are true and a value of 0 if one or none are true
then keeps a total. It's a pretty standard array formula, but I need it
to use wildcards because the second column can contain value A, value B
or value AB. I want the formula to pick up all instances where value A
appears, including where it appears within AB. The formula I have used
is as follows -

=SUM(IF((A1:A1000="string1")*(F1:F1000="A"),1,0))

This works to an extent, but doesn't pick up instances where value A
appears in a cell containing AB as it is looking for only A. If I add a
wildcard "*A*" the formula doesn't work any more... Does anybody have
any suggestions? Any help would be very much appreciated...!!! Thanks
in advance.

James

Choose from these possible array formulas:
=SUM(IF((A1:A1000="string1")*((F1:F1000="A")+(F1:F1000="AB")),1,0))
=SUM(IF((A1:A1000="string1")*(LEFT(F1:F1000,1)="A"),1,0))
=SUM(IF((A1:A1000="string1")*ISNUMBER(FIND("A",F1:F1000)),1,0))
The first finds, specifically, "A" or "AB". The second finds "A" as the
leftmost character (like "A*"). The third finds "A" anywhere (like "*A*").

Note that, as an alternative, you could use one of the equivalent SUMPRODUCT
formulas that don't need to be array-entered:
=SUMPRODUCT((A1:A1000="string1")*((F1:F1000="A")+(F1:F1000="AB")))
=SUMPRODUCT((A1:A1000="string1")*(LEFT(F1:F1000,1)="A"))
=SUMPRODUCT((A1:A1000="string1")*ISNUMBER(FIND("A",F1:F1000)))

 

[Peo Sjoblom]

Why doesn't

=SUMIF(A1:A1000,"*A*",F1:F1000)

work? Works for me..

 

[Jamesmoore]

Guys,

Thanks for your advice on this. Unfortunately it didn't help...

Peo - I can't get your suggestion to work using more than one strin
variable,
=SUMIF(A1:A1000,"*A*",F1:F1000)
would become
=SUM(IF(A1:A1000,"01 - Migrate")(F1:F1000,"GUI"))
This just returns a value of 0 every time.

Paul - Thanks for the comprehensive suggestions. Some of them woul
work, but I should have been clearer in my original question... Ther
is the possibility in the second string of having A, B, AB, AAB, ABBBA
AAABBAB etc... so using an absolute reference won't work. This is why
was trying to use a wildcard, but I can't get it to work with arra
formulae. The formula I have used works fine until I use the wildcar
to include vaules where A occours within a group of Bs etc...

=SUM(IF((A1:A1000="String01")*(F1:F1000="A"),1,0))

This calculates a value where I have String01 and A, but not where
have String01 and BBAA or String01 and ABAAB etc...

For the others I keep getting a 'Circular reference' error...

Any other ideas

 

[Paul]

Guys,

Thanks for your advice on this. Unfortunately it didn't help...

Peo - I can't get your suggestion to work using more than one string
variable,
=SUMIF(A1:A1000,"*A*",F1:F1000)
would become
=SUM(IF(A1:A1000,"01 - Migrate")(F1:F1000,"GUI"))
This just returns a value of 0 every time.

Paul - Thanks for the comprehensive suggestions. Some of them would
work, but I should have been clearer in my original question... There
is the possibility in the second string of having A, B, AB, AAB, ABBBA,
AAABBAB etc... so using an absolute reference won't work. This is why I
was trying to use a wildcard, but I can't get it to work with array
formulae. The formula I have used works fine until I use the wildcard
to include vaules where A occours within a group of Bs etc...

=SUM(IF((A1:A1000="String01")*(F1:F1000="A"),1,0))

This calculates a value where I have String01 and A, but not where I
have String01 and BBAA or String01 and ABAAB etc...

For the others I keep getting a 'Circular reference' error...

Any other ideas?

As I said, my third formula
=SUMPRODUCT((A1:A1000="string1")*ISNUMBER(FIND("A",F1:F1000)))
will find "A" anywhere within the field, such as "BBAA".

You will get a circular reference error if you are putting this formula
within one of the ranges A1:A1000 or F1:F1000, or if some other formula uses
the result of this formula that works its way back into one of these ranges.
To begin with, I suggest you should get the formula working in a cell that
has no dependents (i.e. that isn't referenced by any formula), possibly with
smaller ranges (e.g. A1:A10 and F1:F10).

 

[Jamesmoore]

Paul,

Thank you VERY much for your help. It works perfectly...!
You were right, I was referencing it from within the cell range I ha
specified. At least now I've learned what circular reference error
are!!

Thanks again for your help...

Kind regards,

Jame