+ Reply to Thread
Results 1 to 12 of 12

Using COUNTIF function to compare values

  1. #1
    Registered User
    Join Date
    06-16-2012
    Location
    Iran
    MS-Off Ver
    Excel 2007
    Posts
    5

    Using COUNTIF function to compare values

    Hello there,

    I'm trying to build an excel software and I have a question.
    I have a table like this in my sheet:
    =======
    100 | 100
    200 | 400
    305 | 300
    ... | ....
    =======
    Now I want to count cells in column two with a value greater than column one with a formula. For example the result is 1 for that table.
    How can I do it?
    Thanks!

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,964

    Re: Using COUNTIF function to compare values

    c9
    Please Login or Register  to view this content.
    and drag down.

    Then count the collumn C.

    Or also possible.

    Please Login or Register  to view this content.
    Last edited by oeldere; 06-16-2012 at 09:35 AM.

  3. #3
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Using COUNTIF function to compare values

    Just to be clear on the results you expect, what would be the result based on your example data?

    Do you mean to compare adjacent cells or are you asking for the total number of cells that are greater than the maximum value in the first column?
    Palmetto

    Do you know . . . ?

    You can leave feedback and add to the reputation of all who contributed a helpful response to your solution by clicking the star icon located at the left in one of their post in this thread.

  4. #4
    Registered User
    Join Date
    06-16-2012
    Location
    Iran
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Using COUNTIF function to compare values

    Thanks, this is correct.
    But is there any way else that doesn't need an extra column?

  5. #5
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,964

    Re: Using COUNTIF function to compare values

    Sumproduct (see #2)

  6. #6
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Using COUNTIF function to compare values

    Good day,

    the code in C9 should read

    =if(B9>A9,1,0)

    then = sum(C:C) will give you the count!

    Click on star if your problem is solved!

  7. #7
    Registered User
    Join Date
    06-16-2012
    Location
    Iran
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Using COUNTIF function to compare values

    Oh yes, but I meant the count of the values, not sum of them.
    Is there any way to COUNT cells without extra column?

  8. #8
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Using COUNTIF function to compare values

    =sumproduct(--(b1:b10>a1:a10))

    or
    This array formula

    {=COUNT(IF(B1:B10>A1:A10,B1:B10))}

  9. #9
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,964

    Re: Using COUNTIF function to compare values

    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    06-16-2012
    Location
    Iran
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Using COUNTIF function to compare values

    Yes, this is correct! Thanks for your help, friends!

  11. #11
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,964

    Re: Using COUNTIF function to compare values

    Please reply in your answer to whome youre responding.

    Which formala did you use.

  12. #12
    Registered User
    Join Date
    06-16-2012
    Location
    Iran
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Using COUNTIF function to compare values

    Quote Originally Posted by Palmetto View Post
    =sumproduct(--(b1:b10>a1:a10))

    or
    This array formula

    {=COUNT(IF(B1:B10>A1:A10,B1:B10))}
    This was useful for me.
    Thank you.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1