+ Reply to Thread
Results 1 to 8 of 8

Higher values in Col A than Col B, sum rows with the higher value and show difference

Hybrid View

neilosj Higher values in Col A than... 03-15-2022, 03:51 AM
Glenn Kennedy Re: Higher values in Col A... 03-15-2022, 03:56 AM
neilosj Re: Higher values in Col A... 03-15-2022, 04:18 AM
Glenn Kennedy Re: Higher values in Col A... 03-15-2022, 04:27 AM
Glenn Kennedy Re: Higher values in Col A... 03-15-2022, 04:33 AM
Phuocam Re: Higher values in Col A... 03-15-2022, 04:48 AM
neilosj Re: Higher values in Col A... 03-15-2022, 05:04 AM
Glenn Kennedy Re: Higher values in Col A... 03-15-2022, 05:20 AM
  1. #1
    Registered User
    Join Date
    10-01-2020
    Location
    London, England
    MS-Off Ver
    365
    Posts
    78

    Higher values in Col A than Col B, sum rows with the higher value and show difference

    Hi Community,

    I have two columns of values, I need to know the sum of all the rows where Column A has a higher value than Column B.
    If Column B is higher then ignore.
    There could be 100+ rows of values and I need to apply this to multiple workbooks, but each workbook will have it's own total.

    Any help is appreciated.
    Thanks.
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Higher values in Col A than Col B, sum rows with the higher value and show difference

    Use:

    =SUMPRODUCT(--(A2:A21>B2:B21)*A2:B21)

    adjust the ranges to suit your data.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU

  3. #3
    Registered User
    Join Date
    10-01-2020
    Location
    London, England
    MS-Off Ver
    365
    Posts
    78

    Re: Higher values in Col A than Col B, sum rows with the higher value and show difference

    Thanks for the reply Glen, not quite as it's needed.

    I only need to know the total value of the rows that have a higher value in Column A than in Column B
    In the attached sheet, I have two examples.
    One set is showing the total of column A, Column B and the higher value total of each row.
    Then Highest values minus Column B totals. In the example, Highest values 30 - Column B 26 = 6

    Then other is showing the total of only where Column A is higher than Column B (Actually in Columns F & G) showing 6.

    I need to get 6 from this data.

    Also, there will be other info in between the two columns such as peoples names, preferences, etc.

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Higher values in Col A than Col B, sum rows with the higher value and show difference

    Noted. Your original description "the sum of all the rows" means something entirely different!!!

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Higher values in Col A than Col B, sum rows with the higher value and show difference

    and... according to your expected answers, it is not > but >=....

    So:

    =SUMPRODUCT((IF(A1:A10>=B1:B10,A1:A10,0))+(IF(A1:A10<B1:B10,B1:B10,0)))

    and

    =SUMPRODUCT((IF(F1:F10>=G1:G10,F1:F10-G1:G10,0)))
    Attached Files Attached Files

  6. #6
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2024
    Posts
    6,175

    Re: Higher values in Col A than Col B, sum rows with the higher value and show difference

    Or try this in C12:

    =SUM(IF(A1:A10>=B1:B10,A1:A10,B1:B10))

    in G12:

    =SUMPRODUCT((F1:F10>G1:G10)*(F1:F10-G1:G10))

  7. #7
    Registered User
    Join Date
    10-01-2020
    Location
    London, England
    MS-Off Ver
    365
    Posts
    78

    Re: Higher values in Col A than Col B, sum rows with the higher value and show difference

    Thanks for the solutions. It is working perfectly.

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Higher values in Col A than Col B, sum rows with the higher value and show difference

    Cheers! 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: 15
    Last Post: 12-18-2020, 05:47 AM
  2. Replies: 4
    Last Post: 07-02-2018, 12:52 PM
  3. [SOLVED] Chart - Only show data of higher than zero
    By Jacolene in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 03-11-2017, 06:47 AM
  4. how to show only labels with values higher than 0
    By vito1 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 04-15-2015, 08:51 AM
  5. [SOLVED] Allocate a minimum value to a data set and absorb the difference to higher values
    By nalaka in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 10-28-2012, 01:01 PM
  6. Replies: 3
    Last Post: 09-14-2010, 07:25 AM
  7. Replies: 2
    Last Post: 05-31-2010, 03:23 PM

Tags for this Thread

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