If text already contains a wildcard how do I countif, sumif etc.?

[Steven]

From a database one of the dataset contains a vehicle type for example
pushbike, small van etc. however if the booking is made online it precede the
vehicle type with an * meaning if I countif, sumif pushbike it counts both
types of records.

 

[Don Guillett]

My testing did not produce that result. Post your data and formula

 

[Steven]

Don

Thanks for the quick response, see below formula should return 10 as the
count but returns 20.

Steven

1070175 *Bike
1070177 *Bike
1070179 *Bike
1070182 *Bike
1070183 *Bike
1070188 *Bike
1070191 *Bike
1070192 *Bike
1070193 *Bike
1070194 *Bike
1070195 Bike
1070196 Bike
1070197 Bike
1070198 Bike
1070199 Bike
1070201 Bike
1070202 Bike
1070203 Bike
1070204 Bike
1070205 Bike

=COUNTIF(B2:B21,"*Bike")

 

[Don Guillett]

=SUMPRODUCT((RIGHT(N1:N21,5)="*Bike")*1)

 

[Dave Peterson]

Another one:

=COUNTIF(B2:B21,"~*Bike")

* is the wildcard for any set of characters
? is the wildcard for a single character
~ is the character that tells excel that you want to treat the next character as
a real asterisk, a real question mark or even a real tilde:

=COUNTIF(B2:B21,"~*Bike")
=COUNTIF(B2:B21,"~?Bike")
=COUNTIF(B2:B21,"~~Bike")