COUNTIF
 

I am using the COUNTIF function as follows.

=COUNTIF(Data!AN2:AN533,LOV!A2)

It works for the most part, however, the fields I am looking up
(Data!AN2:AN533) may have more than one value in it.

That is, if LOV!A2 is "Car", Data!AN2, may have "Car, Train,..." or "Train,
Car...).

Is there anyway to use Countif and a wild card value?

Thanks. Happy Holidays.

COUNTIF
 

Look at this:
http://www.lqnet.com/Excel/sumproduct.aspx

Pay attention to these examples:
=SUMPRODUCT((A2:A5="Chicago")*(B2:B5="ABC")*(C2:C5 ))
=SUMPRODUCT((A2:A5="Chicago")*(B2:B5="ABC")*(C2:C5 ="Jan:), (D2:D5), (E2:E5))

Here's a video with a few more examples:
http://www.youtube.com/watch?v=O2ulQwMmlmY

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"PAL" wrote:

I am using the COUNTIF function as follows.

=COUNTIF(Data!AN2:AN533,LOV!A2)

It works for the most part, however, the fields I am looking up
(Data!AN2:AN533) may have more than one value in it.

That is, if LOV!A2 is "Car", Data!AN2, may have "Car, Train,..." or "Train,
Car...).

Is there anyway to use Countif and a wild card value?

Thanks. Happy Holidays.

COUNTIF
 

Not really sure how these would help if there is multiple values in the cell.

"ryguy7272" wrote:

Look at this:
http://www.lqnet.com/Excel/sumproduct.aspx

Pay attention to these examples:
=SUMPRODUCT((A2:A5="Chicago")*(B2:B5="ABC")*(C2:C5 ))
=SUMPRODUCT((A2:A5="Chicago")*(B2:B5="ABC")*(C2:C5 ="Jan:), (D2:D5), (E2:E5))

Here's a video with a few more examples:
http://www.youtube.com/watch?v=O2ulQwMmlmY

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"PAL" wrote:

I am using the COUNTIF function as follows.

=COUNTIF(Data!AN2:AN533,LOV!A2)

It works for the most part, however, the fields I am looking up
(Data!AN2:AN533) may have more than one value in it.

That is, if LOV!A2 is "Car", Data!AN2, may have "Car, Train,..." or "Train,
Car...).

Is there anyway to use Countif and a wild card value?

Thanks. Happy Holidays.

COUNTIF
 

Try something like this...

=COUNTIF(Data!AN2:AN533,"*"&LOV!A2&"*")

Note that it's possible to get "false positives" using this method. Without
knowing what your data looks like it's hard to say if this will be an issue.
For example:

LOV!A2 = car

Data!AN2 = train, cart

This will be counted because the substring car is contained in cart.

--
Biff
Microsoft Excel MVP


"PAL" wrote in message
...
I am using the COUNTIF function as follows.

=COUNTIF(Data!AN2:AN533,LOV!A2)

It works for the most part, however, the fields I am looking up
(Data!AN2:AN533) may have more than one value in it.

That is, if LOV!A2 is "Car", Data!AN2, may have "Car, Train,..." or
"Train,
Car...).

Is there anyway to use Countif and a wild card value?

Thanks. Happy Holidays.

COUNTIF
 

I amy have misunderstood. Try something like this:
=SUMPRODUCT(ISNUMBER(SEARCH("*car*",A1:A5))*((B1:B 5)))

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"T. Valko" wrote:

Try something like this...

=COUNTIF(Data!AN2:AN533,"*"&LOV!A2&"*")

Note that it's possible to get "false positives" using this method. Without
knowing what your data looks like it's hard to say if this will be an issue.
For example:

LOV!A2 = car

Data!AN2 = train, cart

This will be counted because the substring car is contained in cart.

--
Biff
Microsoft Excel MVP


"PAL" wrote in message
...
I am using the COUNTIF function as follows.

=COUNTIF(Data!AN2:AN533,LOV!A2)

It works for the most part, however, the fields I am looking up
(Data!AN2:AN533) may have more than one value in it.

That is, if LOV!A2 is "Car", Data!AN2, may have "Car, Train,..." or
"Train,
Car...).

Is there anyway to use Countif and a wild card value?

Thanks. Happy Holidays.


.