Counting filtered data using wildcard character?

[Steve Bogdanoff]

I am using a formula that counts the number of non-null cells in a filtered data set on range A1:A25. Here is the formula:

=SUMPRODUCT(SUBTOTAL(103,OFFSET(Sheet1!A1:Sheet1!A25,ROW(Sheet1!A1:Sheet1!A25)-MIN(ROW(Sheet1!A1:Sheet1!A25)),0,1,1)),--(Sheet1!A1:Sheet1!A25<>""))

The last argument is what looks for non-null cells.

Question: does anyone know how to change the non-null criteria into one that uses an Excel wildcard character, e.g., looking for all cells that begin with the character "r" .

I tried replacing the (Sheet1!A1:Sheet1!25<>"") argument with ...
(Sheet1!A1:Sheet1A2="r*") but that did not produce the correct result.

Any helpful ideas would be appreciated!
thanks,
Steve Bogdanoff

 

[jasonb75]

Question: does anyone know how to change the non-null criteria into one that uses an Excel wildcard character, e.g., looking for all cells that begin with the character "r" .

I tried replacing the (Sheet1!A1:Sheet1!25<>"") argument with ...
(Sheet1!A1:Sheet1A2="r*") but that did not produce the correct result.

Not all functions accept wildcards, with sumproduct you need to use search (or similar methods).
--ISNUMBER(SEARCH("r",Sheet1!A1:A25))

 

[Fluff]

You could also use

--(LEFT(Sheet1!A1:A25,1)="r")

 

[jasonb75]

You could also use

--(LEFT(Sheet1!A1:A25,1)="r")

As the op is looking for cells that 'begin with' rather than 'contain', this would be a better option.

 

[Steve Bogdanoff]

Fantastic - both of your suggestions work like a charm. Many thanks!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[jasonb75]

Fantastic - both of your suggestions work like a charm. Many thanks!

Just a quick point that I hadn't thought of until @Fluff posted the suggestion to use the LEFT function. As I noted in post 4, you did mention that you wanted to find cells that begin with 'r' in your original question.

The best function to use for what you are doing would depend on how specific the location of the criteria is within the cell.

For a cell that begins with 'r' you would use LEFT
For a cell that contains 'r' anywhere in the cell you would use ISNUMBER(SEARCH(
For a cell that ends with 'r' you would use RIGHT

Note also that none of these are case sensitive so it would see 'r' and 'R' as correct matches, although there are additional alternatives if that should be necessary.