Data from another sheet based on another cell criteria ?

Options

Hello.

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

Answers

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

    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 ✭✭✭
    Options

    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
    Options

    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!