Formula Assistance

Susan van Niekerk
Susan van Niekerk ✭✭✭✭
edited 07/07/23 in Formulas and Functions

Hi All,

I need a formula that will count specific contents of cells with specific text and return a value

I want to know how many times "sam has visited IV in the year ( reporting this in the below sheet)

The info is reported in our team roster - The roster has 1 column per day for the entire calendar year


Answers

  • Debbie Sawyer
    Debbie Sawyer ✭✭✭✭✭✭

    Hi Susan

    You can use a Countif() statement to do this.

    =COUNTIF(Range to look at, Condition to match)

    =COUNTIF([Column1]7:[Column365]7,"IV")

    I indicated row 7 as that was the row that Sam was on in your screen shot!

    Does this help?

    Kind regard

    Debbie

  • Hi Debbie

    That Did work - however when I copy the Formaul to the next row it changes all the rows above it

    what can I do to stop that?

  • Debbie Sawyer
    Debbie Sawyer ✭✭✭✭✭✭

    Hi Susan

    If you want the range to remain the same range but you need to copy the formula to the next column (dragging across a row) then you need to make the Column Reference an Absolute Reference. This fixes the column references within the formula. To do this use a $ in front of the column references.

    Eg

    =COUNTIF($[Column1]7:$[Column365]7,"IV")

    Likewise if it is the Row that you need to fix (ie relate to Sam's data from a different place then you can make the Row Reference an absolute reference instead. This would stop the row numbers changing if you dragged the formula down a column.

    eg

    =COUNTIF([Column1]$7:[Column365]$7,"IV")


    I believe that it would be the first example here that you will need for your analysis table in the screen shot.

    I hope this helps explain.

    Kind regards

    Debbie

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!