COUNT function linked to another sheet
I have a sheet that captures outstanding items for a set of 5 projects that we use to drive weekly status update meetings. I want to create a chart in our dashboard that shows the number of tasks for each project, and possibly one that shows how many have a status of "at risk" or "overdue" which are columns on my sheet.
For the first one, I would think I would use the COUNT function and reference the "Project" column in the original sheet, but something is not working. The function is currently
=COUNT({Regulatory Action Log Range 1}, "ART-123") where Regulatory Action Log Range 1 is the "Project" Column and "ART-123" is the project I want to count. It keeps returning a total of 13 which is the total number of rows in the sheet rather than 4 which is the number of times ART-123 shows up.
For the status count, I have a column with status in it (At Risk or Overdue) and I would think I would need to use the same function or a COUNTIF in the event that I want to know the count of both the project and the status combined (for example, how may at risk items per project).
I know this can all be done simply in a report, but my boss would like something more visual. Is this where a sheet summary might come in handy
Any thoughts are appreciated
Answers
-
You'll just use COUNTIF or COUNTIFS in both cases.
=COUNTIFS({Regulatory Action Log Range 1}, "ART-123")
=COUNTIFS({Regulatory Action Log Range 1}, "ART-123", {Status Range Added Here}, "Status to count"
-
I knew it was something really simple I was missing. Thanks Nic!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!