How do I write a countif nested formula?

Michele @ My School DC

Hi All,

I have looked in the forumula info and I think what I have written will do what I need but it is not working.  I want to count all the times my Ward column is 1 and then my Assistance column says survey.  This is my formula:

="Ward 1=" + COUNTIFS(Ward:Ward, "1", [Assistance 1]:[Assistance 1], "Survey")

 The correct count right now should be 1 (as in there is one survey done for Ward 1) but it says 0.

 

Any thoughts?  And I know I can use reports but I want the team to see a running count on the sheet.

Cheers,

Michele

Michele @ My School DC

I found that I can get the formula to work if I change the wards from numbers to words, so 1 is one.  But I am loathe to try and change 500 entries! And there is no find and replace in Smart Sheet.

 

I would love anyone's suggestions.

Thanks,

Michele

Travis

I just tested your formula and scenario in a sheet and it is working as expected. Could you post a couple screenshots of your formula and the row (showing the Ward/Assistant1 columns) that you expect it to count? Also, do either of these columns contain formulas? What are the column types? 

 

Michele @ My School DC

Travis,

Thanks for the double check. We were correct the formula worked.  I realized that the data set used to pull this data from used words for numbers (one=1) and so in SmartSheet this showed up as '1, '2, etc.  Once I went back to the original and fixed it my formula worked.

 

Pretty much, I did what you said and looked closer at the formatting of my cells and columns. Great to have another set of eyes on it.

Best,

Michele

Travis

Happy to help!