CODEWITHSUNDEEP
excelexcel-formula
Saahar
Saahar

Reputation: 3

Sumifs function not recognizing "true" criteria/column

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())

Upvotes: 0

Views: 495

Answers (2)

AlanE
AlanE

Reputation: 1

Alternatively add a final column for verifying the dates in columns C and D to return true/false or 0/1, then use that column as the second criteria?

Upvotes: 0

Scott Craner
Scott Craner

Reputation: 152585

You will need to shift to an Array Type Formula like SUMPRODUCT:

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

The main drawback to using an Array Type Formula is that it will iterate the references one by one and as such one should limit the range references to the minimum size needed to get the whole dataset.

Upvotes: 1

Related Questions