Counting with 2 criteria

Pamela Wagner

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!

Bassam Khalil

Hi @Pamela Wagner

Hope you are fine, you can create a summary sheet and use the COUNTIFS function to count with unlimited criteria

Pamela Wagner

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.

Mike Wilday

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:

https://help.smartsheet.com/articles/2476606-formulas-reference-data-from-other-sheets

https://www.smartsheet.com/content-center/best-practices/tips-tricks/powerful-and-flexible-cross-sheet-formulas