logo elektroda
logo elektroda
X
logo elektroda
Advanced Search
logo elektroda

Excel: Using IF Formulas for Matching Partial Text, Counting Names, and Filtering Values

wolnyjac21 21891 16
ADVERTISEMENT
Treść została przetłumaczona polish » english Zobacz oryginalną wersję tematu
Reply | New topic
  • #1 13613906
    wolnyjac21
    Level 9  
    Hello,
    My question, why are none of the following formulas true?

    = IF (B26 = "*", 1,0)
    = IF ((B26 = "k *"), 1,0)
    = IF ((B26 = "k" & "*"), 1,0)


    b26 contains the word: smith

    And, in fact, I have one more question.

    In the column from B1 to B50, for example, I have different names and now I do this:
    = If (b1 = "smith", 1,0) - I drag down
    and I am counting up how many of them is the count if function

    My question is: how to do this in one cell?

    And maybe the last one:

    We have a column with the values 0.1
    example
    lp | b
    -------
    1 | 0
    2 | 0
    3 | 1

    and now I would like to enter only those lp for which their value is 1 in column C.
    I know that you can use a filter, but I would like to know if there is also a regulation.
  • ADVERTISEMENT
  • #2 13614194
    JRV
    VBA, Excel specialist  
    Welcome
    1. = IF (FIND ("k", B26) = 1, 1, 0)
    2. = SUM IF (B1: B50, "blacksmith")
  • ADVERTISEMENT
  • #3 13614311
    wolnyjac21
    Level 9  
    1. The find () function will return 7, so your example returns nothing but false and this solution is not suitable for me.
    I would like to check if there is a string with some letter at the beginning and I wonder why the "*" which means any string does not work for me?

    2. This example is also incorrect, or at least it does not meet my requirements
  • Helpful post
    #4 13614361
    JRV
    VBA, Excel specialist  
    1. The find () function is case sensitive.
    2. Sorry - COUNTIF

    ps Among others, the COUNTIF function allows the characters *
  • #5 13615524
    cbrman
    Level 27  
    freejac21 my glass ball prompts you to attach a file with sample data.
  • Helpful post
    #6 13615756
    marek003
    Level 40  
    wolnyjac21 wrote:
    1. The find () function will return 7, so your example returns nothing but false and this solution is not suitable for me.
    I would like to check if there is a string with some letter at the beginning and I wonder why the "*" which means any string does not work for me?

    2. This example is also incorrect, or at least it does not meet my requirements


    It doesn't work for you because excel is mostly made for math and numbers. Excel has completely different functions for operations on characters.
    The condition you applied to the function if is a simple comparison of the value in cell B26 and a string. In your case, a single "asterisk" string. In this case, excel (as well as other spreadsheets) checks whether cell B26 has not entered * (and not anything) because "whatever" is, for example, a function or empty ().



    As for the first one, it is enough to redo what your friend suggested JRV
    = IF (COUNTIF (B26, "k *") = 0; 0; 1)
    Only here (what he mentioned JRV ) there is no case-checking, that is
    this formula will give the same result:
    = IF (COUNTIF (B26, "K *") = 0, 0, 1)

    As for the second (again for a colleague JRV )
    = COUNTIF (B1: B50, "blacksmith")

    And as for the third - it's not that simple.
    Maybe a support column?
    Because if so, you can ultimately use e.g. max k () and in the auxiliary column put ROW ().

    Or if "1" can be replaced immediately with the line number [ROW ()], it is enough in the third column max.k () and that's it
  • #7 13615973
    wolnyjac21
    Level 9  
    Thank you very much for all the answers :)
    Now it remains to fight the sheet, maybe something will work ^^

    and such a question, what mistake am I making here:
    = COUNTIF (G59: G263, IF (LEFT (G59,1) = "k", 1,0))

    ehh somehow, I can't compile the data into one column .. I always have to use 10 helpers; \
  • ADVERTISEMENT
  • #8 13616062
    Maciej Gonet
    VBA, Excel specialist  
    Good morning,
    If there are names in column G, and we want to determine how many of them start with "K", then we should use the formula:
    Code: Text
    Log in, to see the code

    except that this formula does not distinguish between uppercase and lowercase letters. If it were important you would have to use a different formula.
    Regards.
  • ADVERTISEMENT
  • #9 13616154
    wolnyjac21
    Level 9  
    How to sum float numbers, i.e. a floating point number because Sum () returns 0; /
  • #10 13616238
    marek003
    Level 40  
    wolnyjac21 wrote:
    and such a question, what mistake am I making here:
    = COUNTIF (G59: G263 , IF (LEFT (G59,1) = "k", 1,0) )


    First
    "," it is not ";"

    and are you sure this is the counting condition you mean ?:
    IF (LEFT (G59,1) = "k", 1,0)

    As for floating point, format the summed and result cells to floating point and it should be ok. Because in my opinion the sum works, but you don't see the "whole" result.

    Cell format - custom and select e.g. "0.00E + 00"
  • #11 13616259
    cbrman
    Level 27  
    You can use the following table formula to add up the number of surnames beginning with "K"
    Code: Text
    Log in, to see the code
  • #12 13616289
    wolnyjac21
    Level 9  
    marek003 wrote:
    wolnyjac21 wrote:
    and such a question, what mistake am I making here:
    = COUNTIF (G59: G263 , IF (LEFT (G59,1) = "k", 1,0) )


    First
    "," it is not ";"

    and are you sure this is the counting condition you mean ?:
    IF (LEFT (G59,1) = "k", 1,0)

    As for floating point, format the sum and result cells to floating point and it should be ok. Because in my opinion the sum works, but you don't see the "whole" result.

    Cell format - custom and select e.g. "0.00E + 00"


    Well, unfortunately, the excel I'm playing requires commas, not semicolons ... but it's not that important.
    Secondly :) the cells are formatted and the sum is 00.00 I don't know, maybe this excel is bugged ..

    Anyway, thanks a lot for your help ^^
  • #13 13616317
    marek003
    Level 40  
    And what excel is this - version and for which country - UK?

    As for the sum, it is impossible

    0.05 + 0.05 will get you 0.1

    What do you type in the cells?
    And when you press the "increase decimal" button - such an arrow to the left with zeros - nothing happens?

    And thirdly, as you can see "00.00", you have an ordinary - number format

    In the case that I showed you, if it was 0 (zero) he could see not 00.00 but "0.00E + 00"
    for example, with 0.000000000005 you would see "5.00E-12" and with "your" format you just see "00.00" with such a small number
  • #14 13616733
    wolnyjac21
    Level 9  
    Version 2007. As for the country, to be honest, I don't even know, because I installed excel only for a moment (to remind you of certain things) and on Linux.

    Unfortunately, I will not answer you any more questions, because I got rid of excel :)
    Anyway, thank you for your interest in the subject and I hope your advice will not go to the woods ^^
  • #15 17672436
    katharinaa
    Level 9  
    Hello, in column G I have five-digit codes, I want to count those starting with the number 2.
    = IF (COUNTIF (G2: G31; "2 *") = 0; 0; 1)
  • #16 17672472
    Maciej Gonet
    VBA, Excel specialist  
    If these codes are texts then:
    Code: Text
    Log in, to see the code
    If they are numbers then:
    [syntax=text]=LICZ.WARUNKI(A6:A12;">=20000";A6:A12;"
    Attachments:
  • #17 17672683
    katharinaa
    Level 9  
    Maciej Gonet wrote:
    If these codes are texts then:
    Code: Text
    Log in, to see the code
    If they are numbers then:
    [syntax=text]=LICZ.WARUNKI(A6:A12;">=20000";A6:A12;"
Reply | New topic
Report a violation of the law

Topic summary

The discussion revolves around the use of IF formulas in Excel for matching partial text, counting occurrences of names, and filtering values. The user initially inquires why certain formulas using wildcards do not return true when checking for partial matches in cell B26, which contains the word "smith." Responses suggest using the FIND function and COUNTIF for partial text matching, emphasizing that Excel's IF function performs simple comparisons rather than pattern matching. The user also seeks a method to count names in a range (B1:B50) in a single cell, with suggestions to use COUNTIF. Additionally, the user asks how to filter values based on a condition (e.g., displaying only rows where a corresponding value is 1), with recommendations for auxiliary columns and specific formulas. The conversation touches on case sensitivity in functions and formatting issues with floating-point numbers.
Summary generated by the language model.
ADVERTISEMENT