+ Reply to Thread
Results 1 to 4 of 4

Comparing two columns and returning the number of cells in one column with higher values

  1. #1
    Forum Contributor
    Join Date
    06-26-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    136

    Comparing two columns and returning the number of cells in one column with higher values

    I have two Columns C and E that have dollar amounts in them. I want to compare each row in those columns and then total the number in column C that are higher that column E.

    So basically C6>E6, C7<E7, C8=E8,C9>E9 should return the result of 2. There are two instances where the price in C is higher than the price in E.

    Thanks in advance for your help!

  2. #2
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: Comparing two columns and returning the number of cells in one column with higher valu

    To get the total of numbers in col. C which are greater than numbers in col. E, try this array formula. Since this is an array formula so you need to confirm it with Ctrl + Shift + Enter instead of just Enter. (i.e. hold down the Ctrl + Shift and then press Enter)

    Please Login or Register  to view this content.
    To get the count of numbers in col. C which are greater than numbers in col. E try this formula (this is not an array formula, so just confirm it with Enter only)

    Please Login or Register  to view this content.
    You can change the range specified in the formula as per your requirement.
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  3. #3
    Forum Contributor
    Join Date
    06-26-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    136

    Re: Comparing two columns and returning the number of cells in one column with higher valu

    Quote Originally Posted by sktneer View Post
    To get the total of numbers in col. C which are greater than numbers in col. E, try this array formula. Since this is an array formula so you need to confirm it with Ctrl + Shift + Enter instead of just Enter. (i.e. hold down the Ctrl + Shift and then press Enter)

    Please Login or Register  to view this content.
    To get the count of numbers in col. C which are greater than numbers in col. E try this formula (this is not an array formula, so just confirm it with Enter only)

    Please Login or Register  to view this content.
    You can change the range specified in the formula as per your requirement.
    The SumProduct Function was exactly what I was looking for! Thanks

  4. #4
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: Comparing two columns and returning the number of cells in one column with higher valu

    Glad to help you. Thanks for the feedback.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 19
    Last Post: 03-15-2014, 04:50 PM
  2. [SOLVED] Comparing columns for values higher, lower, or equal...
    By D.Lovell in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-18-2013, 07:22 PM
  3. Merge Columns- Keep higher Number if other column is duplcate
    By rocking11 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-14-2012, 11:50 AM
  4. Comparing columns and returning several values in to one row.
    By Chemgeorge in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-20-2010, 08:40 PM
  5. Comparing 2 columns, highlighting the higher number
    By Hagi in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-01-2009, 01:17 PM

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