Sumifs function not recognizing true criteria column

Question

I cannot get my SUMIFS function to recognize one of my criteria. My formula is:

=SUMIFS(A:A,B:B,"=1",C:C,"<="&TODAY(),D:D,"<>"&C:C)

Total    Status   Dock_Date    Ship_Date
10       1        08/27/2018   09/01/2018
20       2        08/25/2018   09/08/2018
10       1        08/27/2018   08/27/2018

Column A - TOTAL B - Status C - Dock Date D- Ship date:

The formula works fine if I leave out the last criteria. i.e. Ship date is not equal to the DOCK date (D:D,"<>"&J:J)

=SUMIFS(A:A,B:B,"=1",C:C,"<="&TODAY())

Answer 1

You must switch to an Array Type Formula, such as SUMPRODUCT.

=SUMPRODUCT(A2:A4,(B2:B4 = 1)*(C2:C4<=TODAY())*(C2:C4<>D2:D4))

The biggest disadvantage of utilizing an Array Type Formula is that it will iterate the references one at a time; as a result, the range of references should be kept to the bare minimum required to obtain the entire dataset.