Search code examples
excel-formulasumifs

Excel SUMIFS formula on using operator inside criteria

SUMIFS(C1:C5,A1:A5-B1:B5,">5")

Is the above formula workable in excel?

Column A and Column B contain dates Column C contains quantity.

What I need is When Column A minus Column B greater than 5 then sum all the quantity.

I understand this is able to do with creating a new column to get the difference in date first then use the computed value inside SUMIFS formula.

However, I really try to avoid adding an extra column.

Thank you

Solution

  • You can use the following formula:

    =SUMPRODUCT(IF((A1:A5-B1:B5)>5,C1:C5))


    this is an array formula, so press ctrl+shift+enter to calculate the formula.



    Hope this works for you.