0

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.

3
0

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.
3
  • Well, I need exactly all criteria . If disposition is "consultation " AND primary is either "Ob"or"surgical" And consultation contains eithe" IM"or "cardio".... – Hassan Almaateeq Jan 20 at 3:00
  • Please check the edited answer. – marikamitsos Jan 20 at 12:06
  • Please remember that as per site guidelines when an answer addresses your question, accept it and even upvote it so others can benefit as well. – marikamitsos Jan 20 at 12:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.