Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

How do I write a countif nested formula?

Michele @ My School DC
edited 12/09/19 in Archived 2015 Posts

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

Comments

  • 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
    Travis Employee

    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? 

     

    Screen Shot 2015-08-14 at 1.21.30 PM.png

    Screen Shot 2015-08-14 at 1.21.42 PM.png

  • 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
    Travis Employee
    edited 08/17/15

    Happy to help! Smile

This discussion has been closed.