EXCEL: COUNTIFS with multiple criteria and or logic

Question

Attempting to count a named range "STATUS" if the value of the cells equals Reserved, Blocked, Pending OR Archive with multiple other countif criteria.

Everything else works, but the array formula I used for counting the status is only returning a count if the cell value = Reserved.

Here is the formula I'm using:

=(COUNTIFS(DATES,">="&A3,DATES,"<"&EDATE(A3,1),AGENT,"TEST",COMPANY,"TEST",STATUS,{"Reserved","Blocked","Pending","Archive"}))

Any help would be greatly appreciated. Thank you in advance.

Sorceri · Accepted Answer · 2017-07-06 17:06:35Z

2

Us Sum to countifs

=SUM((COUNTIFS(DATES,">="&A3,DATES,"<"&EDATE(A3,1),AGENT,"TEST",COMPANY,"TEST",STATUS,{"Reserved","Blocked","Pending","Archive"})))

answered Jul 6, 2017 at 17:06

Sorceri

8,0932 gold badges32 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

airstrike · Accepted Answer · 2017-07-06 16:36:40Z

0

=SUMPRODUCT((DATES>=A3)*(DATES<EDATE(A3,1))*(AGENT="TEST")*(COMPANY="TEST")*((STATUS="Reserved")+(STATUS="Blocked")+(STATUS="Pending")+(STATUS="Archive")))

It would be great if you could provide some sample data or even a screenshot of your sheet.

answered Jul 6, 2017 at 16:36

airstrike

2,4211 gold badge27 silver badges28 bronze badges

2 Comments

DimWSH Over a year ago

I tried this out and received an error message. I cannot provide sample data or screenshots at this time, much of the information is proprietary. Thank you for your effort!

airstrike Over a year ago

This needs to be entered as an array formula to work.

ERT · Accepted Answer · 2017-07-06 17:01:50Z

0

Try using =OR logic.. Replace the following for your STATUS portion

OR(STATUS="Reserved", STATUS="blocked, STATUS="Pending", STATUS="Archive")

EDIT:

Since the previous advice did not work, try this:

(COUNTIFS(DATES,">="&A3,DATES,"<"&EDATE(A3,1),AGENT,"TEST",COMPANY,"TEST",STATUS,OR("Reserved", "Blocked", "Pending", "Archive"))

edited Jul 6, 2017 at 17:01

answered Jul 6, 2017 at 16:34

ERT

7395 silver badges17 bronze badges

4 Comments

DimWSH Over a year ago

placing the OR before the criteria range produces an error message. I placed it before the criteria and that didn't work either, thanks for the suggestion though!

DimWSH Over a year ago

EDIT - I tried placing the OR after the criteria range after your first suggestion and I still received an error message.

ERT Over a year ago

Did you try the exact code I included in the recent edit?

DimWSH Over a year ago

Yes, I did. Thank you.

Collectives™ on Stack Overflow

EXCEL: COUNTIFS with multiple criteria and or logic

3 Answers 3

Comments

2 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related