Dashboard

D. Turner

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.

Paul Newcome

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))

NickStaffordPM

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.

D. Turner

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!

NickStaffordPM

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.

Paul Newcome

@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.

NickStaffordPM

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.

D. Turner

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

Paul Newcome

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

D. Turner

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

Paul Newcome

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))

D. Turner

Thanks!! It was the names matching up to what was on the source sheet!

Paul Newcome

Happy to help. 👍️