# COUNT with wildcards

1. ## COUNT with wildcards

I'm trying to return the number off cells that contain any variation of 23.2 as follows but can't get the correct values to return;

A
1 23.2
2 23.2
3 23.2 one
4 23.2, one
5 23.3

I've tried COUNT(A1:A5,"23.2*") and variations of these using COUNTA, COUNTIF, no wildcards, wildcards *, ? etc but none will return the value I want, in this example it should be 4.

It seems that it should be pretty simple but perhaps the mixture of no's and text is tripping it up or perhaps I'm just using the wrong formula.

2. ## Re: COUNT with wildcards

This works

=SUMPRODUCT(--(ISNUMBER(SEARCH(23.2,A1:A5))))

another solution here

https://exceljet.net/formula/partial...-with-wildcard

3. ## Re: COUNT with wildcards

That works perfectly, many thanks.

4. ## Re: COUNT with wildcards

Try with
Formula:
`Please Login or Register  to view this content.`

5. ## Re: COUNT with wildcards

I've hit another snag, is there a way of combining this with other data, so if I want to return the number of items that are both 23.2 and Done, e.g.;
A. B
1 23.2 Done
2 23.2 Not started
3 23.2 one In progress
4 23.2, one Done
5 23.3 Done

In this example I'd expect to return a value of 2.

6. ## Re: COUNT with wildcards

Thanks,for problem 1 I had tried =COUNTIF(\$A\$1:\$A\$5,"*23.2*")that but it doesn't pick up the fields with both 23.2 and text.

7. ## Re: COUNT with wildcards

Originally Posted by M.G
I've hit another snag, is there a way of combining this with other data, so if I want to return the number of items that are both 23.2 and Done, e.g.;
A. B
1 23.2 Done
2 23.2 Not started
3 23.2 one In progress
4 23.2, one Done
5 23.3 Done

In this example I'd expect to return a value of 2.
=SUMPRODUCT(--(ISNUMBER(SEARCH(23.2,A1:A5)))*(B1:B5="Done"))

As you've added the "Done" parameter you may be able to remove the "--"

=SUMPRODUCT((ISNUMBER(SEARCH(23.2,A1:A5)))*(B1:B5="Done"))

8. ## Re: COUNT with wildcards

That's it, brilliant, thanks so much

There are currently 1 users browsing this thread. (0 members and 1 guests)