Data from another sheet based on another cell criteria ?

Morris R

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

James Keuning

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

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)