Help with COUNTIFS

Ty Werven

Hello,

I am trying to make a smart sheet in the same way as this example template. Form>collector sheet>metrics sheet> dashboard. Basically, I want the end result to be four different graphs tracking monthly results of yes, no, n/a, or other questions. I will include what I have so far and what differs after this example one.

My sheets are below.

Starts with the sheet all form data gets sent to.

Then, each unit will have a separate sheet that gets only their unit responses. Like above.

And then I am on the metrics sheet which will pull from the individual units sheets. The part I am struggling on is getting a COUNTIFS formula to work. As you can see I have the 4 questions broken up and then they have their response under and to the right. What I want to do is just only count each cell that has yes, no, etc. for just that month. For example. the top left cell in the bottom chart is for "Fall Tips Report at bedside". I want to reference the units sheet and count how many said yes and where in the month of January. The cell to the right of that would be for no and for the month of January.

I will include the lines of formula I have tried as well as the examples line of code I tried and did not work. Maybe I was also just doing it wrong?

My two examples above I have tried messing with. The examples from the template below that I also could not get to work.

Scott Orsey

@Ty Werven , I'll start with your second version which I prefer... There is something wrong with the syntax. You can see that the $[Month#]@row should light up in a color. In your first attempt you referred to the month number column as [Primary Column], so I would check to make sure that the column name is correct.

I see the value of your first attempt because you can pick up the yes/no from the header. HAS() is not a great way to go. The second approach would be the better place to start. If you want to pick up the header value in the second approach, then replace the "yes" with [column3]$9 so that it can be copied down and across.

I hope this helps. Be well!