Using comparators in google sheets countif within an arrayfunction

Question

I have a sheets formula that presently looks like this:

=ARRAYFORMULA(IF(ROW(J:J)=1,"eduProjects",IF($A:$A="",,
COUNTIF(Projects!$B:$B&Projects!$R:$R&Projects!$S:$S,$A:$A&"Education"&0)+
COUNTIF(Projects!$B:$B&Projects!$R:$R&Projects!$S:$S,$A:$A&"Education"&1)+
COUNTIF(Projects!$B:$B&Projects!$R:$R&Projects!$S:$S,$A:$A&"Education"&2)+
COUNTIF(Projects!$B:$B&Projects!$R:$R&Projects!$S:$S,$A:$A&"Education"&3)+
COUNTIF(Projects!$B:$B&Projects!$R:$R&Projects!$S:$S,$A:$A&"Education"&4)+
COUNTIF(Projects!$B:$B&Projects!$R:$R&Projects!$S:$S,$A:$A&"Education"&5)+
COUNTIF(Projects!$B:$B&Projects!$R:$R&Projects!$S:$S,$A:$A&"Education"&6)+
COUNTIF(Projects!$B:$B&Projects!$R:$R&Projects!$S:$S,$A:$A&"Education"&7)+
COUNTIF(Projects!$B:$B&Projects!$R:$R&Projects!$S:$S,$A:$A&"Education"&8)+
COUNTIF(Projects!$B:$B&Projects!$R:$R&Projects!$S:$S,$A:$A&"Education"&9)+
COUNTIF(Projects!$B:$B&Projects!$R:$R&Projects!$S:$S,$A:$A&"Education"&10)+
COUNTIF(Projects!$B:$B&Projects!$R:$R&Projects!$S:$S,$A:$A&"Education"&11)+
COUNTIF(Projects!$B:$B&Projects!$R:$R&Projects!$S:$S,$A:$A&"Education"&12)+
COUNTIF(Projects!$B:$B&Projects!$R:$R&Projects!$S:$S,$A:$A&"Education"&13)+
COUNTIF(Projects!$B:$B&Projects!$R:$R&Projects!$S:$S,$A:$A&"Education"&14)+
COUNTIF(Projects!$B:$B&Projects!$R:$R&Projects!$S:$S,$A:$A&"Education"&15)+
COUNTIF(Projects!$B:$B&Projects!$R:$R&Projects!$S:$S,$A:$A&"Education"&16)+
COUNTIF(Projects!$B:$B&Projects!$R:$R&Projects!$S:$S,$A:$A&"Education"&17)+
COUNTIF(Projects!$B:$B&Projects!$R:$R&Projects!$S:$S,$A:$A&"Education"&18))))

I'm trying to simplify this by using a comparator "<19" in place of all the numbers 0 to 18

=ARRAYFORMULA(IF(ROW(L:L)=1,"eduProjects",IF($A:$A="",,
COUNTIF(Projects!$B:$B&Projects!$R:$R&Projects!$S:$S,$A:$A&"Education"&"<19"))))

Unfortunately, this doesn't seem to work in an array. Any ideas how I can simplify this formula?

Answer 1

Your formula:

COUNTIF(Projects!$B:$B&Projects!$R:$R&Projects!$S:$S,$A:$A&"Education"&"<19")

• Countif() criterion basically becomes a concatenated string of column A + Education + <19.

---

If my understanding is correct that Projects!$S:$S should contains a number value since you concatenate it with Projects!$B:$B & Projects!$R:$R and tried to compare it with $A:$A&"Education"&0 concatenated string value.

Then you can use COUNTIFS() to simplify your formula:

=ARRAYFORMULA(IF(ROW(L:L)=1,"eduProjects",IF($A:$A="",,
COUNTIFS(Projects!$B:$B&Projects!$R:$R,$A:$A&"Education",Projects!$S:$S,"<19"))))

• Check concatenated Projects!$B:$B & Projects!$R:$R if it matches concatenated $A:$A & "Education".
• Check if Projects!$S:$S is <19

---

Output:

(B1 contains your original formula, while E1 contains the simplified formula)

(Projects Sheet)