Count Multiple Cells Formula Question

Hi! I am not sure I am doing this right, I am second guessing myself. I am referencing another sheet and this if the formula I am using:

=COUNTIFS({PM1}, "Yes", {Date}, MONTH({Date} = $[Month#]@row)) + COUNTIFS({PM22}, "No", {Date}, MONTH({Date} = $[Month#]@row)) + COUNTIFS({PM3}, "No", {Date}, MONTH({Date} = $[Month#]@row))

I want it to count the column PM1 when the answer is "Yes" when the month is January (which is my @row) AND PM22 when the answer is "No" and the month is January AND PM3 when the answer is "No" and the month is January.

Is this correct? Or is the + telling it to count when ALL of those parameters are met, or is the + like a period would be in a sentence where it notices they are separate things to count. Does that make sense?

Find more posts tagged with

Accepted answers

All comments

Ryan Sides

@tmichelle068

You were on track.

The + is saying add the numbers of the results of the COUNTIFS together.

=COUNTIFS({PM1}, "Yes", {Date}, MONTH(@cell) = $[Month#]@row) + COUNTIFS({PM22}, "No", {Date}, MONTH(@cell) = $[Month#]@row) + COUNTIFS({PM3}, "No", {Date}, MONTH(@cell) = $[Month#]@row)

tmichelle068

Thank you! It works fine when I enter the formula where all the counts are for "No" answers. But in every category where I want to count "No" and "Yes" answers, I get an #invalidoperation message. Any ideas why?

Example:

This formula works: =COUNTIFS({RE 1}, "No", {Date}, MONTH({Date} = $[Month#]@row)) + COUNTIFS({RE 2}, "No", {Date}, MONTH({Date} = $[Month#]@row)) + COUNTIFS({RE4}, "No", {Date}, MONTH({Date} = $[Month#]@row))

This formula does not: =COUNTIFS({SIS 2}, "No", {Date}, MONTH({Date} = $[Month#]@row)) + COUNTIFS({SIS 3}, "Yes", {Date}, MONTH({Date} = $[Month#]@row))

And this formula does not even though it is all "No" answers, what am I messing up here: =COUNTIFS({BM 1}, "No", {Date}, MONTH({Date} = $[Month#]@row)) + COUNTIFS({BM 2}, "No", {Date}, MONTH({Date} = $[Month#]@row))

Ryan Sides

You may have blank cells in there. Try the formula below. You can edit your other formulas to reflect these changes too...

=COUNTIFS({SIS 2}, "No", {Date}, IFERROR(MONTH(@cell), 0) = MONTH($[Month#]@row)) + COUNTIFS({SIS 3}, "Yes", {Date}, IFERROR(MONTH(@cell), 0) = MONTH($[Month#]@row))

tmichelle068

I did have some blank spots with what I was trying to reference, so thank you! I corrected that, so now referencing columns with Date and Answers (which all have data in them now) and this formula still isn't working...

=COUNTIFS({MED2}, "No", {Date}, MONTH({Date} = $[Month#]@row)) + =COUNTIFS({MED 5}, "Yes", {Date}, MONTH({Date} = $[Month#]@row))

Here is what it looks like, I want to count the times Med2 is answered "No" and Med5 is answered "Yes" during the month of the row.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.