Data from another sheet based on another cell criteria ?



Hoping to get data count based on criteria from another cell , from another sheet?

From another sheet i am trying to count the occurrences "PCM" and "ICM" appear on completed or cancelled projects only. Trying to get the "ACTIVE" projects that involve those 2 teams.

thank you in advance


  • James Keuning
    James Keuning ✭✭✭✭✭
    edited 05/16/24

    I would put a formula in a checkbox column in that sheet like:

    =AND(OR(CONTAINS("ICM", [Row A]@row), CONTAINS("PCM", [Row A]@row)), NOT(OR([Row Status]@row = "complete", [Row Status]@row = "cancelled")))

    And then use COUNTIF and cross-sheet reference to go over there and count how many TRUEs you have.

  • Jgorsich
    Jgorsich ✭✭✭

    I would think that this would work:

    =countifs({projectStatus},"Active", {rowA},"PCM")+countifs({projectStatus},"Active",{rowA},"ICM")

    sometimes it is easier to just add to simple countifs than to try to figure out a single formula with REALLY efficient and tricky criteria.

  • Jason Hamman

    It'd help to see the sheet you want to pull this information into and its purpose.

    If the goal is to summarize information, there may be simpler options:

    • Summary Report: A quick way to get at a glance information about your data (more info)
    • Sheet Summary: An in-sheet feature where you can setup custom metric names (e.g. - "Active Work" / "Completed Work") and calcs (COUNTIFS in this case) to get at a glance totals. (more info)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!