COUNTIFS / Referencing data another sheet
I'm still pretty new to Smartsheet. My goal is collect data from another sheet in an easier to read format but I cannot get my COUNTIFS formula to work. If I do separate formulas for each criteria it will work but not when I attempt to combine the formulas to look at 2 criteria's it fails.
I have columns on the referencing sheet with Team Names (accounting, finance, compliance, etc) and Status column (On Time, Complete, Late, etc). I have inserted references on that range of columns.
Reference sheet is "Program1"
=COUNTIFS({Program1}, "Accounting", {Program1}, "Complete")
=COUNTIFS ({Program1}, "Accounting", {Program1}, "Late")
I want a total count of when the team name has a task showing as complete, late, etc.
I have researched up and down and cannot get anything other than an "unparseable" return.
What am I missing? Any help is appreciated.
Comments
-
You need to set the range on the reference sheet. Typically when you reference a portion of another sheet in a formula it would be something to the effect of {Program1 Range 1} to look something like this in a formula:
=COUNTIFS({Program1 Range 1}, "Accounting", {Program1 Range 2}, "Complete")
The easiest way to accomplish this is by selecting the "Reference Another Sheet" link in the help box that pops up as you type your formula (see images below and please disregard the sloppy lines as I am certainly NOT an artist. Haha).
Otherwise, your formulas seem to be pretty well set.
-
Thank you the ranges were the key. I was selecting a single range of multiple columns. Once I split it up it worked. Thanks!
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
- 146 Just for fun
- 63 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!