Dashboard
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.
Best Answer
-
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))
Answers
-
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 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!
-
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.
-
@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.
-
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.
-
The formula should look at the sheet mentioned, and the name to see how many times they are listed, correct?
-
That is correct. Do you have a screenshot of the source data? Can you provide more details such as column types?
-
It is looking at this Investigator Assigned column to count how many times the name is assigned. The column is a "contact list" type.
-
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))
-
Thanks!! It was the names matching up to what was on the source sheet!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!