Forum Discussion
Compare 2 columns and sum the larger value in column
Hello, I need help again. This is my formula: =SUM(IF('01-24 DB'!$AK19:$AK156>'01-24 DB'!$AN19:$AN156,'01-24 DB'!$AK19,$AN20))
I would like to compare column AK and AN, and if AK is > AN then i will sum the value. If no, i wouldn't. So essentially, it should only sum up the values IF AK > AN.
Thank you so much!!
Thank you.
If you have Microsoft 365:
=SUM(BYROW('01-24 DB'!$AK19:$AN156, LAMBDA(r, MAX(INDEX(r, 1), INDEX(r, COLUMNS(r))))))
- sorry, i think i explained it wrongly!
I would like to compare column AK and AN, and if AK is > AN then i will sum the value. If no, i wouldn't. So essentially, it should only sum up the values IF AK > AN.
Thank you so much!!If you want to sum those values in AK that are larger than the corresponding value in AN:
=SUM('01-24 DB'!$AK19:$AK156*('01-24 DB'!$AK19:$AK156>'01-24 DB'!$AN19:$AN156))
or
=SUM(IF('01-24 DB'!$AK19:$AK156>'01-24 DB'!$AN19:$AN156, '01-24 DB'!$AK19:$AK156))
that should work except that last value is $AN20 instead of '01-24 DB'!$AN19
the '01-24 DB'! is only needed if that is on a different sheet but row 20 instead of 19 would throw the answer off.
Alternatively you could use other options like:
=SUM('01-24 DB'!$AK19:$AK156, '01-24 DB'!$AN19:$AN156), ABS('01-24 DB'!$AK19:$AK156 - '01-24 DB'!$AN19:$AN156))/2
