SUM COUNTIFS with criteria over multiple ranges

[samleighcattrall]

I am struggling to get the below formula to work

=SUM(COUNTIFS(Salesforce!$P:$P,">="&B7,Salesforce!$P:$P,"<="&B10,Salesforce!$D:$D,">=30",Salesforce!$V:$V,"Kieran",Salesforce!$J:$J,'Specialists + PS'!$A$3:$A$8,Salesforce!$F:$F,'Specialists + PS'!$E$3:$E$5))+SUM(COUNTIFS(Salesforce!$P:$P,">="&B7,Salesforce!$P:$P,"<="&B10,Salesforce!$D:$D,">=30",Salesforce!$V:$V,"Kieran",Salesforce!$J:$J,'Specialists + PS'!$A$3:$A$8,Salesforce!$F:$F,'Specialists + PS'!$E$9,Salesforce!$H:$H,'Specialists + PS'!$H$2:$H$5))

However if I swap out the red sections with what's written in the range instead, it returns the correct values. You could say that fixes the issues, however if I want to change those ranges in the future I need to edit the formula.

=SUM(COUNTIFS(Salesforce!$P:$P,">="&B7,Salesforce!$P:$P,"<="&B10,Salesforce!$D:$D,">=30",Salesforce!$V:$V,"Kieran",Salesforce!$J:$J,'Specialists + PS'!$A$3:$A$8,Salesforce!$F:$F,{"Built to Curtilage","Network Built","Built"}))+SUM(COUNTIFS(Salesforce!$P:$P,">="&B7,Salesforce!$P:$P,"<="&B10,Salesforce!$D:$D,">=30",Salesforce!$V:$V,"Kieran",Salesforce!$J:$J,'Specialists + PS'!$A$3:$A$8,Salesforce!$F:$F,'Specialists + PS'!$E$9,Salesforce!$H:$H,{"23/24 Q4","23/24 Q3","23/24 Q2","23/24 Q1"}))

 

[RoryA]

It's because you are already using a vertical array as criteria for another column, so you need a horizontal one for the second array:

=SUM(COUNTIFS(Salesforce!$P:$P,">="&B7,Salesforce!$P:$P,"<="&B10,Salesforce!$D:$D,">=30",Salesforce!$V:$V,"Kieran",Salesforce!$J:$J,'Specialists + PS'!$A$3:$A$8,Salesforce!$F:$F,TRANSPOSE('Specialists + PS'!$E$3:$E$5)))+SUM(COUNTIFS(Salesforce!$P:$P,">="&B7,Salesforce!$P:$P,"<="&B10,Salesforce!$D:$D,">=30",Salesforce!$V:$V,"Kieran",Salesforce!$J:$J,'Specialists + PS'!$A$3:$A$8,Salesforce!$F:$F,'Specialists + PS'!$E$9,Salesforce!$H:$H,TRANSPOSE('Specialists + PS'!$H$2:$H$5)))

or you could use FILTER since you have 365.

 

[samleighcattrall]

It's because you are already using a vertical array as criteria for another column, so you need a horizontal one for the second array:

=SUM(COUNTIFS(Salesforce!$P:$P,">="&B7,Salesforce!$P:$P,"<="&B10,Salesforce!$D:$D,">=30",Salesforce!$V:$V,"Kieran",Salesforce!$J:$J,'Specialists + PS'!$A$3:$A$8,Salesforce!$F:$F,TRANSPOSE('Specialists + PS'!$E$3:$E$5)))+SUM(COUNTIFS(Salesforce!$P:$P,">="&B7,Salesforce!$P:$P,"<="&B10,Salesforce!$D:$D,">=30",Salesforce!$V:$V,"Kieran",Salesforce!$J:$J,'Specialists + PS'!$A$3:$A$8,Salesforce!$F:$F,'Specialists + PS'!$E$9,Salesforce!$H:$H,TRANSPOSE('Specialists + PS'!$H$2:$H$5)))

or you could use FILTER since you have 365.

Thank you Rory, I appreciate the above formula only includes two vertical ranges per sum, but if I had say 4 vertical ranges what would be the best solution?

 

[RoryA]

FILTER. You cannot do that with a COUNTIFS.