COUNTIFS Referencing Other Sheets
Needing help with the COUNTIFS and referencing other sheets. It keeps coming back #unparseable
I am trying to find the number of projects in a certain phase based on who the project is assigned to.
Active Leads Range 11: Assigned to
Active Leads range 10: Phase
. =COUNTIFS({Active Leads Range 11}:{Active Leads Range 11}, "Mark",{Active Leads Range 10},"Bidding")
I am sure I am completely off, but any help would be appreciated.
Comments
-
Hi Jill,
You were actually really close! When you specify a range on the source sheet (e.g. Active Leads), you only need to reference the range (i.e. not specify it again) on the target sheet as the range already includes information about what sheet, columns and rows you're matching criteria against.
That means your formula will look like this:
=COUNTIFS({Active Leads Range 11}, "Mark",{Active Leads Range 10}, "Bidding")
Hope that helps!
Cheers,
Chris -
Chris -
Thank you! That helps a lot. One more question - is there a way to reference 2 sheets in 1 formula.
I have 1 sheet that is for Active Leads and 1 sheet that is for Active Projects. I would like to combine the count based on the same data for both of these sheets.
=COUNTIFS({Assigned To}, "Mark", {Phase}, "New Lead", {Active Projects - Assigned To}, "Mark", {Active Projects - Phase} "New Lead")
The formula above is coming back #unparseable again... Thoughts?
-
Hi Jill,
I see you've got the hang of creating named ranges. Well done.
In answer to your question, while you can certainly reference more than one sheet in a formula, I don't believe Smartsheet cross-sheet references (the { } references) allow you to do what you want inside a COUNTIF/COUNTIFS statement.
If you are looking to combine the count (i.e. add up all the times Mark is assigned to an Active Lead or Active project), you can do this:
=COUNTIFS({Assigned To}, "Mark", {Phase}, "New Lead") + COUNTIFS({Active Projects - Assigned To}, "Mark", {Active Projects - Phase} "New Lead")
Kind regards,
Chris McKay
-
I have a question on this same line.
I have a list of Projects that also includes that Data Services person that is on the project. I want to get an active count of the total Projects assigned to both the Project Manager and the Data Services Engineer. I have made the following in a new sheet:=COUNTIFS({Project Management - Load}, "Adam Beach",{Project Management - Load}, "In Progress")
I get back a response of #INVALID REF.
What am I doing wrong?
-
Hi Burke,
Can you describe your process in more detail and maybe share the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)
Hope that helps!
Have a fantastic week!
Best,
Andrée Starå
Workflow Consultant / CEO @ WORK BOLD
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Hello,
I was following this thread as I am trying to create a countifs formula to add data from 2 separate sheets. I've tried multiple formulas to see if I could make it work, but I continue to get an "Unparseable" error or an "Incorrect Argument Set" error.
Unparseable error is with:
=COUNTIFS({2021 L2R After Hours Raw Data Range 1}, "L2R After Hours Inquiry- Tuition", {2021 L2R After Hours Raw Data Range 3}, "Name", {2021 L2R After Hours Inquiry- Tuition Open Range 1}, "{2021 L2R After Hours Inquiry- Tuition Open Range 2}, "Name")
Incorrect Argument Set error is with:
=COUNTIFS({2021 L2R After Hours Inquiry- Tuition Open Range 2}, "Name", {2021 L2R After Hours Inquiry- Tuition Open Range 3}, "L2R After Hours Inquiry- Tuition Open" + COUNTIFS({2021 L2R After Hours Raw Data Range 3}, "Name", {2021 L2R After Hours Raw Data Range 1}, "L2R After Hours Inquiry- Tuition"))
Overall, I am trying to determine the number of Tuition and Tuition Open inquiries for a person. Hoping someone can help.
Thanks,
Rebecca
-
For your first formula, it looks like there's an additional quotation mark in front of your final range:
here > "{2021 L2R After Hours Inquiry- Tuition Open Range 2}, "Name")
However, since you're looking into two different sheets, you will need to add together two COUNTIF statements like you have set up in your second option, instead of combining them in one formula. Each individual formula will need to look into just one sheet, then you can add the result together.
Your second formula needs to have each COUNTIF function closed off with one of these ) before you add in the next COUNT. Try this:
=COUNTIFS({2021 L2R After Hours Inquiry- Tuition Open Range 2}, "Name", {2021 L2R After Hours Inquiry- Tuition Open Range 3}, "L2R After Hours Inquiry- Tuition Open") + COUNTIFS({2021 L2R After Hours Raw Data Range 3}, "Name", {2021 L2R After Hours Raw Data Range 1}, "L2R After Hours Inquiry- Tuition")
Cheers!
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Hi Genevieve,
Thanks so much!
-
No problem at all, @Rebecca Lane! 🙂
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 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!