Formula Assistance

Susan van Niekerk

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

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

Susan van Niekerk

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

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