countifs for total column instances when two conditions are met

Question

I'm trying to set a counter across two sheets in my Google Sheets workbook. I want to count the number of rows in sheet "Referrals" where Column C has "sixth" as its value AND "LIKES Submission" as the value on Column G.

I tried =arrayformula(SUM((Referrals!C:C="sixth")+(Referrals!G:G="LIKE Submission"))), but the result is incorrect as I don't want to add instances of each condition independently--rather the number of instances where both conditions are true. Moreover, considering the advice on this question I tried =countifs(Referrals!C:C,"sixth",G:G,"LIKE Submission"), but I'm getting an error Array arguments to COUNTIFS are of different size.

In this sample, I'd expect the result from the viewable rows to be 3 (three rows contain both "sixth" and "LIKE Submission")

Any help will be well received.

Answer 1

The formula you have chosen is appropriate =COUNTIFS(criteria_range1, criterion1, [criteria_range2, …], [criterion2, …]), the problem is with your implementation.

In your example formula criteria_range1 and criteria_range2 are on different sheets. You are using entire columns to specify the number of rows in each range, but your sheets have different numbers of rows in them. Per the error message, the arrays you are combining can't be of different sizes.

You can work around this issue by

• limiting the length of the two ranges to the same number of rows =countifs(Referrals!Cx:Cy,"sixth",Gx:Gy,"LIKE Submission") where 'x=first_row' and 'y=last_row'; or
• by making sure both sheets have the same number or rows by inserting or removing rows.

Take a look at this sample Google spreadsheet ¹, where I have illustrated the issue in the following cells:

1. Test!A1
   Column length mismatch (error)
2. Test!A2
   Column length mismatch, but rows defined (success)
3. Test!A3
   Column lengths match (success)

---

_{¹ https://docs.google.com/spreadsheets/d/1jHsWrYTjXtTQdt6Nnf1c5qCWuR3ixSQCsb6JUQIBlZU/edit#gid=2010405637}