# Dashboard

Options

I have a dashboard roll-up sheet that is using a COUNTIF

=COUNTIFS({sheetname}, [Column2]10)

formula to count the number of cases an employee has. I know the employee has cases on the sheet, but the field shows zero, instead of the number of cases that are assigned to them.

• ✭✭✭✭✭
Options

Hello!

So COUNTIF is Range of search then criteria, so your formula would look something like this:

=COUNTIFS([Column Your Are Searching In]:[Column Your Are Searching In],"Value You Are Searching For")

If you wanted to reference a cell within a row as the criteria, you would use the [Column2]@row instead of the "Value You Are Selecting For".

Your formula is not doing what you want because you do not have a column selected in the first part of the synthax, just a sheet name.

If this does not help please let me know.

Thanks!

Nick Stafford

• Options

Thanks for the response Nick!

That original formula was looking at the sheet named and then referencing the cell [Column2]@row for the name to search for on the sheet. Then count how many times that name is referenced on that sheet.

I hope that I am explaining this well!

• ✭✭✭✭✭
Options

No problem!

What I am saying though is that is not how COUNTIF works. you need to identify a specific column you are looking in, then specific the value it should look for. It will then count the occurances.

Thanks!

Nick Stafford

• ✭✭✭✭✭✭
Options

@NickStaffordPM That is exactly what the original formula is doing. The {Cross Sheet Reference} is the range, and the criteria is looking for a match in the cell reference of [Column2]10. This is exactly how the COUNTIF and COUNTIFS functions are designed to work.

@D. Turner Are you able to provide some screenshots for reference? More details would be helpful such as column types and whatnot.

• ✭✭✭✭✭
Options

Oops I didn't recognize it as a cross sheet reference, just thought it referred to the entire sheet. I learned something new!

D you are in good hands with Paul.

Thanks!

Nick Stafford

• Options

The formula should look at the sheet mentioned, and the name to see how many times they are listed, correct?

• ✭✭✭✭✭✭
Options

That is correct. Do you have a screenshot of the source data? Can you provide more details such as column types?

• Options

It is looking at this Investigator Assigned column to count how many times the name is assigned. The column is a "contact list" type.

• ✭✭✭✭✭✭
Options

Double check the spelling is an exact match in both sheets and then try a HAS function like so:

=COUNTIFS({sheetname}, HAS(@cell, [Column2]10))

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!