Search code examples
arraysexcelexcel-formulasumifs

Sumif values across a range into 1 cell

HI I have a have two columns with numbers. If the right column is greater than the left I want the differences summed. The thing is I need them in one column. I have tried to sumif, but it just keeps taking the first cell in the second column

SUMIF(A1:A5,">"&$B1$1:$B$5,B1:B5)

   A    B
1  1    1
2  2    2
3  4    2
4  5    4
5  1    4             output=3
Solution

  • Use SUMPRODUCT():

    =SUMPRODUCT(--(A1:A5>B1:B5),A1:A5-B1:B5)