Counting with 2 criteria
I am creating a sheet report where my primary column is departments and the following columns are criteria that I'm interested in reviewing how many times the department is utilizing that criteria. For example, unit 9a: how many times did we support a discharge; number of times we transfered; number of times XYZ. How do I create a formula that looks at what the department is on this sheet and then goes to the master sheet and counts the number of times that criteria was assigned to the department. Here is a screenshot of the report (note that the data is manually entered so I could see how it would work in a graph):
Here is what the mocked up graph will look like:
I'm also open to how to show 28 separate depts and all of this data so that it is not so cumbersome on a dashboard. Not only are there 28 depts but there are 19 potential criteria!
Answers
-
Hi @Pamela Wagner
Hope you are fine, you can create a summary sheet and use the COUNTIFS function to count with unlimited criteria
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
Hi @Bassam.M Khalil , thanks but that is what I'm doing and I'm stuck on the reference since one is on the summary sheet and one is on the master sheet. I'm sure it's much easier than I'm making out.
-
I would start with a COUNTIFS Forumula. You're going to need to do cross sheet references to pull the data from that other sheet. Basically, when you start your formula you will use COUNTIFS( then select in the dropdown help box, use a cross-sheet reference). Then you can build out your formula to account for your needs.
Here is more on cross-sheet references:
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!