Google Sheets – Fix Arrayformula Function Error Due to Different Sizes

formulasgoogle sheetsgoogle-sheets-arrayformularegex

I'm trying to sum rows with specific criteria with the following formula:

=ArrayFormula(sum(countifs(disposition,"Consultation" ,Primary,{"","OB_Gyn","Surgical"},Consultations,{"*IM*","*Cardio*","*Nephro*","*Neuro*","*GI*",""})))

where disposition, Primary, Consultations are NamedRanges of columns with same rows 9-58.
But I get the error message.

#value!
Array arguments to COUNTIFS are of different size.

Best Answer

EDIT (following OP's comment

I need exactly all criteria . If disposition is "consultation " AND primary is either "Ob"or"surgical" And consultation contains eithe" IM"or "cardio"....

You can easily alter the formula with something like the following

=ArrayFormula(sum(
                N(REGEXMATCH({disposition, Primary, Consultations},"xx|yy|Neuro|GI")),
                N(REGEXMATCH({disposition},"zz|ABD")),
                N(REGEXMATCH({Primary},"Cardio|Nephro")),
                COUNTBLANK(disposition),
                COUNTBLANK(Primary)
                                        ))

The basic difference is COUNTBLANK that we bring in our formula because we can not count blanks using the regex.

Original answer

Instead of your countifs formula you could try the following

=ArrayFormula(SUM(N(REGEXMATCH({disposition, Primary, Consultations}, 
                                  "IM|Cardio|Nephro|Neuro|GI"))))

What the formula does

  1. Using the REGEXMATCH in combination with the ArrayFormula function we come up with TRUE or FALSE results.
  2. Because though, TRUE and FALSE correspond to 1 and 0 values, we use the N function to turn them to 1s and 0s respectively.
  3. Finally we SUM to get the desired result.
Related Topic