Sumifs function not recognizing true criteria column

0 votes

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())
Sep 27, 2022 in Others by Kithuzzz
• 38,010 points
339 views

1 answer to this question.

0 votes

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.

answered Sep 29, 2022 by narikkadan
• 63,420 points

Related Questions In Others

+1 vote
0 answers
0 votes
1 answer
0 votes
1 answer
0 votes
1 answer

TypeError: $.ajax(...) is not a function?

Please double-check if you're using the full-version ...READ MORE

answered Feb 22, 2022 in Others by Aditya
• 7,680 points
4,761 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hello, yes u can find your birthdate using ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
1,160 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hi To Calculate the date, we can ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
1,392 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17, 2022 in Others by Edureka
• 13,670 points
686 views
0 votes
1 answer

IF - ELSE IF - ELSE Structure in Excel

In this case, you can use nested ...READ MORE

answered Feb 18, 2022 in Others by gaurav
• 23,260 points
2,264 views
0 votes
1 answer

Is there a way to SUMIFS with multiple criteria + true or false?

You can add this as the  last criteria: =SUMIFS(STORE!$C$6:$C$1000;STORE!$A$6:$A$1000;""&SUMMARY!$D$5&"";STORE!$D$6:$D$1000;""&SUMMARY!$C$9&"";STORE!$E$6:$E$1000;""&SUMMARY!D8&"";STORE!$AA$6:$AA$1000;TRUE) STORE!$AA$6:$AA$1000: ...READ MORE

answered Oct 1, 2022 in Others by narikkadan
• 63,420 points
754 views
0 votes
1 answer
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP