Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Countif | Excel Discussion (Misc queries) | |||
How do I use a countif function according to two other countif fu. | Excel Worksheet Functions | |||
edit this =COUNTIF(A1:F16,"*1-2*")+COUNTIF(A1:F16,"*2-1*") | Excel Discussion (Misc queries) | |||
COUNTIF or not to COUNTIF on a range in another sheet | Excel Worksheet Functions | |||
COUNTIF in one colum then COUNTIF in another...??? | Excel Worksheet Functions |