1. Forum
  2. Usenet
  3. MICROSOFT.PUBLIC.EXCEL

  • COUNTIF() calculates the wrong value

    From Martin Halliday@21:1/5 to All on Fri Mar 4 11:19:49 2022
    If I fill cells A1:A4 in a spreadsheet with the values:
    "< 4 hours"
    "4-8 hours"
    "1-3 days"
    "> 3 days"
    Then in A5 I enter the formula =COUNTIF(A1:A4,A4)
    The calculated result is 4 even though clearly only A4 matches, and the count should be 1.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From David@21:1/5 to Martin Halliday on Sat Mar 5 12:06:47 2022
    On Friday, March 4, 2022 at 2:19:53 PM UTC-5, Martin Halliday wrote:
    If I fill cells A1:A4 in a spreadsheet with the values:
    "< 4 hours"
    "4-8 hours"
    "1-3 days"
    "> 3 days"
    Then in A5 I enter the formula =COUNTIF(A1:A4,A4)
    The calculated result is 4 even though clearly only A4 matches, and the count should be 1.

    Hi,
    I am not sure exactly what you are trying to accomplish. All of the cells are filled with text and the numbers are meaningless.

    =COUNTIF(A1:A4, "> days") in cell A4 gives me 4. I am testing for "> days" or " > days".

    Thanks, David

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Martin Halliday@21:1/5 to All on Sun Mar 6 21:25:19 2022
    What I am trying to accomplish is to count the number of cells in a rage that contain a specific value - which is exactly what the COUNTIF function is for.

    In my example if A1:A4 all contain different values (which they do in my example) and I count how many of these cells contain the same value as A4, then the result should be 1 because A1, A2 and A3 do not contain the same value as A4, and only A4
    contains the same value as A4.

    I think this is pretty simple to understand, and I hope you can grasp the concept now.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From malone@21:1/5 to Martin Halliday on Mon Mar 7 19:38:28 2022
    On 7-Mar-2022 6:25 pm, Martin Halliday wrote:
    What I am trying to accomplish is to count the number of cells in a rage that contain a specific value - which is exactly what the COUNTIF function is for.

    In my example if A1:A4 all contain different values (which they do in my example)
    no, they don't. There are no values there - just text
    and I count how many of these cells contain the same value as A4, then the result should be 1 because A1, A2 and A3 do not contain the same value as A4, and only A4 contains the same value as A4.

    I think this is pretty simple to understand, and I hope you can grasp the concept now.
    perhaps you need to grasp the concept of the difference between text and values?


    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)