Help with an array formula - wild card in formula 1

[creeker69]

One of the most powerful and best tricks I have ever learned from this forum was how to properly use the Array Formula. But I running into a problem recently that I thought could be solved easily using a Wild Card (*,?,~).

To be simple, here's my formula that results in "0".

{=SUM(IF(WHEREFROM = "CURB*",1))} If I remove the asterick, it will properly and count all the "CURB" values, but I need it to count all the CURB* items.

Because the text in this column may occasionally be a value that only STARTS with CURB and may not always be *-EAST or *-WEST, I prefer not to nest more conditions in the array.

WHEREFROM is a defined range containing about 8 different text values (CURB, CURB-EAST,CURB-WEST,TRADE,AUCTION, etc.)

However, the old COUNTIF works fine
=COUNTIF(WHEREFROM="CURB*")

The reason that I need the wildcard to work inside the ARRAY Formula is that it will be part of a much longer ARRAY Formula that SUMs values of other ranges, etc.

I thank you all in advance!!

creeker69

 

[AnotherHiggins]

An array formula loops through all the cells in an range and evaluates each one. The heart of your formula is
[tab]WHEREFROM = "CURB*"
which, when evaluating a single cell, can be replaced with
[tab]A1 = "CURB*"

That does not return true if A1 is CURB, CURB-EAST, etc.

You need to put a formula at the heart that will return TRUE/FALSE depending on whether is cell contains the string CURB.

There are several ways you could do that. The first thing that comes to my mind is =IsNumber(Search("CURB",A1)).

To convert the returned TRUE/FALSE to numbers (1's and 0's, respectively) which can then be counted, just multiply by one.

You'll wind up with something like this:
[tab]{=SUM(1*(ISNUMBER(SEARCH("CURB",A2:A6))))}

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[xlbo]

You can use:

{=SUM(IF(LEFT(WHEREFROM,4) = "CURB",1))}

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[creeker69]

Thanks to both of you! John explained why it does not work but placing that solution in the very long array formula I already have confused me a little, though it works perfectly by itself.

Geoff, I can't believe i didn't think of that simple little extra of "LEFT". That is a very very easy solution and and does the trick!!!!!

I find that the hardest thing posting is make sure others understand the problem. You both did and both solved it!

Thanks to both of you - without your support this forum would not be half the fun!

creeker69

 

[AnotherHiggins]

I'm glad you got it sorted, creeker69.

Just to follow up about how my suggestion works....

=Search("CURB",A1) will return the number of the character at which the "C" in "CURB" is found within A1. If A1 does not contain the string "CURB", then an error is returned. So wrapping the SEARCH function within ISNUMBER will return "TRUE" for each cell that contains "CURB" and "FALSE" for each cell that does not.

One important thing to keep in mind is that it will return TRUE no matter where in A1 "CURB" appears. That is, aaaCURBaaa would return "TRUE".

So if WHEREFROM could ever contain, say, TRADE-CURB, my function would count it and Geoff's function would ignore it.

Note: Search and Find are similar, but Search is not case sensitive and Find is case sensitive


[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[xlbo]

very true John

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[yogia]

Hi creek69:

In addition to fine contributions from John, and Geoff, you may also try ...

=SUMPRODUCT(--(LEFT(A2:A6,4)="curb"))

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com