Countif and error question

tmichelle068

I have had some issues with a formula and I think I FINALLY figured out what is wrong. I want to count the times a question is answered "No" so I am using this formula:

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

I keep getting #invalidoperation and think it is because some of the rows have a blank space where the question is skipped on the form. I think I should use an error formula, but haven't done that before. Can someone help me with how that should look?

Paul Newcome

Initially your syntax is off. It should read more like this:

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

But you are correct that blanks in the date column will cause a problem, and that can be taken care of like so:

=COUNTIFS({PM22}, "No", {Date}, IFERROR(MONTH(@cell), 0) = $[Month#]@row)

tmichelle068

Thank you! The blanks are not in the date column though, they are in the PM22 column. I will actually have several columns I reference in the formula to count the "No" or "Yes" answer, but some of the rows in that column will be blank.

So, overall this is what the formula I have looked like,

=COUNTIFS({PM22}, "No", {Date}, MONTH(@cell) = $[Month#]@row) = COUNTIFS({PM3}, "No", {Date}, MONTH(@cell) = $[Month#]@row) = COUNTIFS({PM44}, "No", {Date}, MONTH(@cell) = $[Month#]@row) = COUNTIFS({PM5}, "No", {Date}, MONTH(@cell) = $[Month#]@row) = COUNTIFS({PM6}, "No", {Date}, MONTH(@cell) = $[Month#]@row)

and now I am getting an #invalidcolumnvalue error. This is a form where the first question determines whether they have to answer questions 2-6; I want to count the "No" answers on questions 2-6, but if they answer question 1 as a "No" they don't have to answer 2-6 leaving them blank. I think those blanks are messing up my formula, just not sure how to fix it.

Paul Newcome

Blanks in the other columns shouldn't matter then. Just the date column since you are using the MONTH function.

The syntax correction for that range/criteria set should work for you.

tmichelle068

Here is where I am trying to enter the formula to calculate violations per month using this formula: =COUNTIFS({PM22}, "No", {Date}, MONTH(@cell) = $[Month#]@row) = COUNTIFS({PM3}, "No", {Date}, MONTH(@cell) = $[Month#]@row) = COUNTIFS({PM44}, "No", {Date}, MONTH(@cell) = $[Month#]@row) = COUNTIFS({PM5}, "No", {Date}, MONTH(@cell) = $[Month#]@row) = COUNTIFS({PM6}, "No", {Date}, MONTH(@cell) = $[Month#]@row)

Here is what the sheet I am gathering that data from on these columns looks like:

You can see when PM1 is "No" then 2-6 are blank. I'm not sure why I am still getting an error. I had this formula previously loaded and it was working fine, then one day it just wasn't.

tmichelle068

Also, I get this #invalidcolumnvalue error on each section of my sheet I try this on, not just these few columns. I have double checked the answers are part of the drop down values, and the column type is drop down. The formula works fine when I am only using data from one column, but when I try to add the other columns from that section in I am getting the error.

Jeff Reisman

@tmichelle068 Are the values in your Date column actually stored as dates? The column type should be date, and when you click on the cells, you should see the little blue calendar icon on the right side of the cell:

tmichelle068

Yes, It is a Date column and I see the calendar icon.

I even tried making a new date column and doing a dropdown of months (because really that is all I am concerned with for this data) and added January 2023, February 2023, etc and am still getting an #invalidcolumnvalue error

Jeff Reisman

@tmichelle068 What about the [Month#] column that has your month numbers in it? Are those stored as numbers? The #INVALID COLUMN VALUE error comes from trying to use a function against a column value that is of the wrong type.

In looking at your formula,there are just so many equal signs that don't seem like they have a place!

=COUNTIFS({PM22}, "No", {Date}, MONTH(@cell) = $[Month#]@row) = COUNTIFS({PM3}, "No", {Date}, MONTH(@cell) = $[Month#]@row) = COUNTIFS({PM44}, "No", {Date}, MONTH(@cell) = $[Month#]@row) = COUNTIFS({PM5}, "No", {Date}, MONTH(@cell) = $[Month#]@row) = COUNTIFS({PM6}, "No", {Date}, MONTH(@cell) = $[Month#]@row)

This whole thing is what's in this cell?

Are you trying to add a bunch of COUNTIFS together? Because if you are, those equal signs need to be right up against the COUNTIFS, and there should be plus signs between each formula:

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

Jeff Reisman

@tmichelle068

You could also try doing your counts on each individual row on the data sheet, by using a hidden helper "NoCount" column:

=COUNTIF([PM1]:[PM44], "No")

This will count all the No instances in the range from column PM1 to column PM44 on a given row.

Then, on your summary sheet:

=SUMIFS({NoCount column Range}, {Date}, MONTH(@cell) = $[Month#]@row)

This should add up all the values in the NoCount column where the Date is in the month for that summary sheet row. If you get an error with this formula, try this:

For January: =SUMIFS({NoCount column Range}, {Date}, MONTH(@cell) = 1)

For February: =SUMIFS({NoCount column Range}, {Date}, MONTH(@cell) = 2)

etc.

tmichelle068

I thought about the hidden helper column as well, I think that is what I may do!

Will I have to go in periodically and add the formula to new rows, or is there a way I could have it auto-add the formula as rows are added. I have only dabbled with automated workflow, is this something I could do in there?

Paul Newcome

@Jeff Reisman I think if we are adding a bunch of COUNTIFS together, there should only be the one = at the beginning.

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

Jeff Reisman

https://community.smartsheet.com/discussion/comment/361581#Comment_361581

You're probably right. All them equal signs threw me for a loop! 😵

Jeff Reisman

https://community.smartsheet.com/discussion/comment/361548#Comment_361548

=COUNTIF([PM1]:[PM44], "No") should work as a column formula. Enter the formula in one row, then right click and down at the bottom of the context menu, select "Convert to column formula." Then every existing row, and every new row, gets the formula applied.

If you periodically add new PM columns, just place an empty column at the far right and call it "PMEND" or something. Set your formula to be =COUNTIF([PM1]:[PMEND], "No"). Then when you create new PM columns, as long as they are to the left of PMEND and to the right of PM1, they will automatically be included in the count.