excelexcel-formulardl

SUM Column based on Calculation Excel

I have the following table:

------------------------------------
Delivered     Return       Remain
------------------------------------
3             3            0
3             0            3
4             2            2
------------------------------------

Let say, the header column is start on Column A and Row 1.
So I want to have the total of Delivered column. =SUM(A2:A4). Now, I want to have another total of Delivered column with some condition. The condition is, only total if A2 - B2 is not zero (0).

Does anyone have an idea for this?
Really Appreciated. Thank you.

Solution

  • Couple ways. This should work:

    =SUMPRODUCT(--((A:A)<>(B:B))*(A:A))

    If you have 2019 Excel or later you could do...

      =Sum(Filter(A:A,(A:A<>B:B))*(IsNumber(A:A)))