CODEWITHSUNDEEP
arraysexcelexcel-formulasumcountif
DimWSH
DimWSH

Reputation: 55

EXCEL - Multiple Arrays within COUNTIF formula

Attempting to count:

Named range "STATUS" if the value of the cells equals Reserved, Blocked, Pending OR Archive

AND

Named range "COMPANY" equals Company A, Company B OR Company C.

I'm also counting multiple other criteria, all of the which are working. When I added the array for the Company the formula is no longer counting the Status.

Here is the formula I'm using:

=SUM((COUNTIFS(DATES,">="&A19,DATES,"<"&EDATE(A19,1),AGENT,"JOHN DOE",COMPANY,{"COMPANY A","COMPANY B","COMPANY C"},STATUS,{"Reserved","Blocked","Pending","Archive"})))

Any help is greatly appreciated and thank you in advance!

Upvotes: 2

Views: 990

Answers (2)

Mrig
Mrig

Reputation: 11727

Try this

=SUM((COUNTIFS(DATES,">="&A19,DATES,"<"&EDATE(A19,1),AGENT,"JOHN DOE",COMPANY,{"COMPANY A";"COMPANY B";"COMPANY C"},STATUS,{"Reserved","Blocked","Pending","Archive"})))

or 

=SUM((COUNTIFS(DATES,">="&A19,DATES,"<"&EDATE(A19,1),AGENT,"JOHN DOE",COMPANY,{"COMPANY A","COMPANY B","COMPANY C"},STATUS,{"Reserved";"Blocked";"Pending";"Archive"})))

Note the ; instead of , in arrays. For details on why to use semi-colon instead of comma see this.

If you want to use your formula it should be as follows

=SUM((COUNTIFS(DATES,">="&A19,DATES,"<"&EDATE(A19,1),AGENT,"JOHN DOE",COMPANY,"COMPANY A",STATUS,{"Reserved","Blocked","Pending","Archive"}))) +
SUM((COUNTIFS(DATES,">="&A19,DATES,"<"&EDATE(A19,1),AGENT,"JOHN DOE",COMPANY,"COMPANY B",STATUS,{"Reserved","Blocked","Pending","Archive"}))) + 
SUM((COUNTIFS(DATES,">="&A19,DATES,"<"&EDATE(A19,1),AGENT,"JOHN DOE",COMPANY,"COMPANY C",STATUS,{"Reserved","Blocked","Pending","Archive"})))

Upvotes: 2

Aprillion
Aprillion

Reputation: 22304

you can create additional column with all of the conditions you need as if inside an IF condition, e.g. 

=(A2>=A19) * ((B2="COMPANY A") + (B2="COMPANY B")))

and then =COUNTIF(ALL_CONDITIONS, ">0")

Upvotes: 0

Related Questions