Sum together the larger amount of two ranges by row

missingsc

New Member
Joined
Mar 14, 2024
Messages
25
Office Version
  1. 365
Platform
  1. Windows
  2. Web
I have to believe this has been solved before, but I could not find a previous post for my particular problem, so apologies if I missed a previous one. I'm trying to use SUMIF/SUMIFS or some variation of formula to look at two different columns of price amounts, determine for each row which of the price amounts is larger, and then sum the larger of those amounts together. Basically wanting the largest possible sum between the two columns of data. Here's some mock data for an example:
AB
100200
300100
400800
500250
200400

So, for example, I would sum these ranges and it would say:
  • Row 1: 200 is larger than 100
  • Row 2: 300 is larger than 100
  • Row 3: 800 is larger than 400
  • Row 4: 500 is larger than 250
  • Row 5: 400 is larger than 200
  • Final amount is 2200.
I tried some variations of SUMIF and SUMIFS but could not get it to work. Any help you can provide would be greatly appreciated!
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Few options:
Book1
ABCDE
1
210020022002200
3300100
4400800
5500250
6200400
Sheet1
Cell Formulas
RangeFormula
D2D2=SUM(BYROW(A2:B6,MAX))
E2E2=SUM(IF(B2:B6>A2:A6,B2:B6,A2:A6))
 
Upvote 0
Thank you! The first option works great, but I forgot to specify that the columns are not contiguous. How would you use the BYROW option if the amounts to compare are in columns A and C instead?
 
Upvote 0
You can stack them together with HSTACK.
Excel Formula:
=SUM(BYROW(HSTACK(A2:A6,C2:C6),MAX))
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top