Using Countifs in differents sheets and related with 2 columns

Hello everyone!

I need some help. I'm trying to match a two formulas for each column, similar like these,

|In Progress|

=COUNTIFS({Progress}, <1, {Start}, >=DATE(2021, 05, 1), {Start}, <=DATE(2021, 05, 30)) ---- Result should be "2" since there're 2 dates in the column finish date with the month on May. (It should be counting as well the cell in blank "ISBLANK", that I colored in "gray", since has a start date).

|Implemented|

=COUNTIFS({Progress}, =1, {Finish}, >=DATE(2021, 05, 1), {Finish}, <=DATE(2021, 05, 30)) --- Result should be "3" since there're 3 dates in the column finish date with the month on May.

Now, if a date from previous month is in progress and I terminate in other month, should be subtracted in the column of progress and sum in the month in which the task was completed.

The sheet on the right has the numbers that I´m trying to formulated, those are the correct results that i'm trying to get.

It would be amazing if we can add some a CHILDREN function on this...

I hope I made myself understood,

Many thanks

Diego Cárdenas

Find more posts tagged with

Formulas

Accepted answers

Genevieve P.

Hi @Diego Cardenas

The first thing I would do is add a "helper column" in your main source sheet to identify what level each row is at (if it's a Child row or not).

The formula I used is like so:

=COUNT(ANCESTORS([Primary Column]@row))

This will tell me if each row has a Parent or not:

Screen Shot 2021-10-11 at 10.58.05 PM.png

I would set this as a Column Formula. Now we can use it as a criteria in your cross-sheet formulas!

IN PROGRESS:

=COUNTIFS({Progress}, OR(@cell < 1, @cell = ""), {Level}, 2, {Start}, >=DATE(2021, 5, 1), {Start}, <=DATE(2021, 5, 31), {End}, "")

Note that I'm checking to see if the Progress Cell is blank (= "") as well as if it's less than 100%.

I'm also checking that the Level is 2, or a Child Row, and it has to have a Blank End Date. This means that when an End Date is added to the sheet it will subtract from In Progress.

Implemented

=COUNTIFS({Progress}, =1, {Level}, 2, {End}, >=DATE(2021, 5, 1), {End}, <=DATE(2021, 5, 31))

Let me know if this work for what you wanted to calculate!

Cheers,

Genevieve

All comments

Genevieve P.

Hi @Diego Cardenas

The first thing I would do is add a "helper column" in your main source sheet to identify what level each row is at (if it's a Child row or not).

The formula I used is like so:

=COUNT(ANCESTORS([Primary Column]@row))

This will tell me if each row has a Parent or not:

I would set this as a Column Formula. Now we can use it as a criteria in your cross-sheet formulas!

IN PROGRESS:

=COUNTIFS({Progress}, OR(@cell < 1, @cell = ""), {Level}, 2, {Start}, >=DATE(2021, 5, 1), {Start}, <=DATE(2021, 5, 31), {End}, "")

Note that I'm checking to see if the Progress Cell is blank (= "") as well as if it's less than 100%.

I'm also checking that the Level is 2, or a Child Row, and it has to have a Blank End Date. This means that when an End Date is added to the sheet it will subtract from In Progress.

Implemented

=COUNTIFS({Progress}, =1, {Level}, 2, {End}, >=DATE(2021, 5, 1), {End}, <=DATE(2021, 5, 31))

Let me know if this work for what you wanted to calculate!

Cheers,

Genevieve

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.