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
Best Answer

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 crosssheet 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
Need more help? 👀  Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋  Global Discussions
Answers

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 crosssheet 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
Need more help? 👀  Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋  Global Discussions
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.8K Get Help
 410 Global Discussions
 219 Industry Talk
 457 Announcements
 4.7K Ideas & Feature Requests
 141 Brandfolder
 136 Just for fun
 57 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 297 Events
 37 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!