Count Multiple Cells Formula Question

tmichelle068

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?

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.