0

I would like to Count Multiple Criteria from one column in addition to a single piece of criteria from 2 other columns in one tab into a count on a separate tab of the same workbook in google sheets. I am using this to track progress in a project.

I have hacked together a formula that isn't working correctly:

=((COUNTIFS(CCSS,"Number & Operations in Base Ten",Grade,{"K","1","2"},GoalStatus,{"Assigned to Writer","Goal Ready for Quality Review","Goal Quality Review Complete","Goal Ready for Copy Edit","Copy Edit Complete"})))

I was trying to have the following happen:
COUNTIF Criteria CCSS (Column AD) is "Number & Operations in Base Ten"
AND
Grade Criteria (Column E) is K, 1, OR 2
AND
any of the following criteria are found in the GOALStatus Criteria (column B) {"Assigned to Writer","Goal Ready for Quality Review","Goal Quality Review Complete","Goal Ready for Copy Edit","Copy Edit Complete"}.

1 Answer 1

0

I think it would be easier to approach it this way:

=COUNTA(FILTER(CCSS, CCSS="Number & Operations in Base Ten", REGEXMATCH(Grade&"","K|1|2"), REGEXMATCH(GoalStatus,"Assigned to Writer|Goal Ready for Quality Review|Goal Quality Review Complete|Goal Ready for Copy Edit|Copy Edit Complete")))

Here, we FILTER in only the results we want and then COUNTA them. The pipe symbol (|) in each REGEXMATCH means OR.

2
  • Wow, thanks Erik that totally solved my problem!!
    – Adera
    Aug 12, 2021 at 15:19
  • Glad to know it worked. In case you are wondering, by the way, I appended &"" to Grade in that REGEXMATCH because it appears some of your Grade entries may have been pure numbers (e.g., 1, 2), and REGEX-type functions only work on strings. Appending a null character is a way to be sure that all entries will be seen as strings instead of numbers.
    – Erik Tyler
    Aug 12, 2021 at 15:36

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.