"Array arguments to COUNTIFS are of different size" - counting the number of rows in Sheets with multiple criteria

I am new to Sheets so this might be a straightforward problem. I have a sheet with editions in various states and types. I am trying to count how many editions were created before a particular date for a certain state and type. Example:

I have a formula in sheets using COUNTIFS but I get the error:

Array arguments to COUNTIFS are of different size.

This is my formula:

= Arrayformula(SUM(COUNTIFS('Sheet1'!$F$2:$F$2000 ,{"bug","Cust_issue"},'Sheet1'!$G$2:$G$2000 ,"<" &DATE, 'Sheet1'!$E$2:$E$2000, {"assigned","accepted","new","fixed","verified"})))

What am I doing wrong? The ranges are all the same.

Here's the issue: your ranges are indeed of the same size, but the criteria inside the curly brackets {} must also be the same in number.

I'm sure there are more elegant solutions, but the simplest one would be to repeat values to artificially match the largest criteria set:

= Arrayformula(SUM(COUNTIFS('Sheet1'!$F$2:$F$2000, {"bug","Cust_issue","bug","Cust_issue","bug"},'Sheet1'!$G$2:$G$2000, "<"&DATE(2024, 10, 03), 'Sheet1'!$E$2:$E$2000, {"assigned","accepted","new","fixed","verified"})))

Notice two things:

1. your formula will match the exact criteria strings you pass to it - albeit being case-insensitive. So right now, the "cust issue" in your sample table wouldn't be matched since it is not the same than cust_issue;
2. I added today's date to the DATE function since, being a function, it must take arguments in the DATE(year, month, day) format.

EDIT - Here is an alternative formula which uses regular expressions that you could try:

=ROWS(FILTER('Sheet1'!$E$2:$G$2000, REGEXMATCH('Sheet1'!$F$2:$F$2000, "^(?i)(?:bug|Cust_issue)$"),'Sheet1'!$G$2:$G$2000 < DATE(2024, 10, 03), REGEXMATCH('Sheet1'!$E$2:$E$2000, "^(?i)(?:assigned|accepted|new|fixed|verified)$")))

References

• REGEXMATCH
• FILTER
• ROWS