SUMIFS function not recognizing a criteria

[saahar]

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 DOCK date (D:D,"<>"&J:J)
=SUMIFS(A:A,B:B,"=1",C:C,"<="&TODAY())

[BadlySpelledBuoy]

What is your expected result?

BSB

[saahar]

Expected Result = 10
What i get = 20

It should ignore the last column since the ship is equal to dock date

[rorya]

It's not doing what you need. You could use SUMPRODUCT instead:

=SUMPRODUCT(A1:A1000,(B1:B1000=1)*(C1:C1000<=TODAY())*(D1:D1000<>C1:C1000))

Note: don't use entire columns!

[Special-K]

Works fine for me, I get 10.

Your data is up the creek.

[BadlySpelledBuoy]

Rory beat me to it. SUMIFS doesn't let you compare a range against a range as per your last argument in the formula but SUMPRODUCT will.

However, when I replicate your data and formula I get 10 rather than the 20 you get.

BSB

[rorya]

Depends which row you enter the formula in, since you get an implicit intersection.

[saahar]

Thanks for the help. The SUMPRODUCT function works. I did not know that SUMIFS doesn't let you compare a range.

[BadlySpelledBuoy]

Depends which row you enter the formula in, since you get an implicit intersection.

Aha, of course!

BSB