Countif formula with Cell reference

I would like to build a dynamic formula:

COUNT IF

  1. 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

  1. 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

AMEXGLOBALBUSINESSTRAVEL.COM

follow us on twitter: @AmexGBT

follow us on instagram: @AmexGBT

Answers

  • SteyJ
    SteyJ ✭✭✭✭✭✭
    edited 11/17/23

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!