Filter and Count Formula

Trisha

Hi!

I am having a little trouble with a data field I am trying to create. (See below)

I have figured out the formulas to calculate the first line (Master)

What I need help with is a CountIFS formula for the others. These are on a master sheet and there is a column for workstream. I need to pull out only those Workstreams and count using the same logic as line 1.

Thanks!

Genevieve P.

Hi @Trisha

A COUNTIFS (plural) function works the same as a COUNTIF (singular). All you need to do is list the next {cross sheet range}, comma, then the next "Criteria".

In your case, you already have your sheet set up with the criteria in the Title column, which is great! This means you can reference that cell for the workstream to search for instead of typing it directly into your formula.

For your "Not Started" column, try a structure like this:

=COUNTIFS({Status Column}, "Not Started", {Workstream Column}, Title@row)

Because we're referencing the cell to the left with "Title@row", this means you can drag the formula down into your other rows and it will dynamically update to look for the workstreams you've listed.

Then you can copy/paste this into your "In Progress" column and simply swap out what you're searching for in the Status range:

=COUNTIFS({Status Column}, "In Progress", {Workstream Column}, Title@row)

See: Create cross sheet references to work with data in another sheet and Create a Cell or Column Reference in a Formula

Let me know if this makes sense or if I can help clarify anything further!

Cheers,

Genevieve