Sum Values That Are Less Than Or Equal To Values In Another Column

[jhudson444]

Hi. I need help with modifying a formula that I created.

Current formula: =SUMIFS(B:B,B:B,"<="&A:A)

How can I modify this formula to allow me to enter any value in column B and to only sum values that are not greater than the values in column A?

Ex:

.....A.....B
1...2......1 < value to sum = 1
2...2......2 < value to sum = 2
3...2......3 < value to sum = 2
4...5......4 < value to sum = 4
5...7......9 < value to sum = 7

Sum of column B = 1+2+2+4+7 = 16

[FDibbins]

Based on the sample provided, Im not sure I follow your logic?
4...5 you select 4 (1st value < 2nd value, you select 1st value)
5...7 you select 7 (1st value < 2nd value, you select 2nd value)

Maybe a helper column with this, and then sum the helper?
=IF(B1>=A1,A1,B1)

[jhudson444]

Sorry, allow me to rewrite the table:

A.....B
2.....1 < value to sum = 1
2.....2 < value to sum = 2
2.....3 < value to sum = 2
5.....4 < value to sum = 4
7.....9 < value to sum = 7

If the value in column B is less than or equal to the value in column A, then sum the value in column B. If the value in column B is greater than the value in column A, than sum the value in column A.

[FDibbins]

as suggested, a helper with this copied down...
=if(B1<=A1,B1,A1)
then sum the helper

[azumi]

Just upload your dummy workbook and your goal bro....

[shg]

=SUM(IF(A1:A5 < B1:B5, A1:A5, B1:B5))

Confirmed with Ctrl+Shift+Enter

[jhudson444]

Thanks, that works!

Is there a way to rewrite that formula so that it does not have to be entered as an array, or is an array formula the only way to calculate sums like this?

[shg]

=sumproduct((a1:a5<=b1:b5)*(a1:a5) + (a1:a5>b1:b5)*(b1:b5))

[jhudson444]

Thank you and everyone else for your help!