Countif formula with Cell reference
I would like to build a dynamic formula:
COUNT IF
- A field in a column of my reference table contains - not necessarily matches - the value in a specific cell of my calculation sheet. (E.g. I entered the term "EPIC A" in the specific cell in my calculation sheet (instead of typing the actual word into the formula) and would like the formula to considers all lines that contain the term "EPIC A" in that column. )
AND
- The value in another column of my reference table matches the value in a field in my calculation sheet (E.g. I have the status "In Progress", "Done", and "Aborted". Instead of typing the words in each line formula, I would like to refer to the cell containing the word.
As a result I have a somewhat dynamic calculation sheet where a user only needs to adjust wording in select cells to modify - without changing the formulas.
Here is an example (however, there is an error in the formula and it is UNPARSABLE
=COUNTIF(AND({PDB Master v1.0 - Initiative Column} CONTAINS([Output (Calculation)]120),{PDB Master v1.0 Status COlumn} Input@row))
Is that possible?
Thank you!
SYLVIA KAY | DIRECTOR PROJECT MANAGEMENT
PLATFORM ENGINEERING & TECHNOLOGY TEAM
AMERICAN EXPRESS GLOBAL BUSINESS TRAVEL
M: 404-664- 1025 | SYLVIA.KAY@AMEXGBT.COM
follow us on twitter: @AmexGBT
follow us on instagram: @AmexGBT
Answers
-
If I'm reading correctly, I believe you just need to count with multiple criteria, this can be done with COUNTIFS
=COUNTIFS({PDB Master v1.0 - Initiative Column}, CONTAINS([Output (Calculation)]120, @cell), {PDB Master v1.0 Status COlumn}, Input@row)
Does this work?
Sincerely,
Jacob Stey
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!