EXCEL: COUNTIFS with multiple criteria and or logi

问题:

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.

回答1:

Us Sum to countifs

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

回答2:

=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.

回答3:

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