| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
If text already contains a wildcard how do I countif, sumif etc.?
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If text already contains a wildcard how do I countif, sumif etc.?
My testing did not produce that result. Post your data and formula
-- Don Guillett Microsoft MVP Excel SalesAid Software "Steven" wrote in message ... 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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If text already contains a wildcard how do I countif, sumif et
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" wrote: My testing did not produce that result. Post your data and formula -- Don Guillett Microsoft MVP Excel SalesAid Software "Steven" wrote in message ... 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. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If text already contains a wildcard how do I countif, sumif et
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") Steven wrote: 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" wrote: My testing did not produce that result. Post your data and formula -- Don Guillett Microsoft MVP Excel SalesAid Software "Steven" wrote in message ... 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. -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Wildcard with sumif | Excel Discussion (Misc queries) | |||
| COUNT or COUNTIF using wildcard text? | Excel Worksheet Functions | |||
| Need help on countif and sumif function with dates and wildcard characters | Excel Discussion (Misc queries) | |||
| Sumif using wildcard | Excel Worksheet Functions | |||
| SUMIF, wildcard and cell ref | Excel Worksheet Functions | |||
Hybrid Mode
