Better formula method of tracking statistics by row  it clutters up the sheet too much.
We have a sheet that tracks assets and the process of their development. We also have a report that shows how many assets and associated tasks are coming up in the next 8 weeks (by week) for allocating resources.
I have added numerous columns that calculate (based on dates) when the asset is due and how many tasks associated with the asset are due in the coming weeks. Here's an example:
For each row I look to see if there are Tasks or Assets that are still due and what week it's due (this is the formula for tasks due 5 weeks out):
=COUNTIFS([Calc if Done]@row, =0, [End Date]@row, AND(@cell > TODAY(+28), @cell <= TODAY(+35)))
At the Parent row and the top row I sum these calculations for reporting. I also use Summary fields to total some cells.
This all works fine. Here's my question:
All of these columns I use to calculate whether a task or asset is still due and what week into the future it will be due really clutters up the sheet. I know I can hide columns but it's still pretty messy. Is there a better way to track this information without having all these columns in the sheet?
Is there a way to ONLY use Summary fields that I haven't found?
Here's a shot of the sheet for perspective:
Thanks,
Jeana
Best Answer

OK. If you want to use Sheet Summary fields instead, you would set up a summary field for each week and then use a COUNTIFS such as
=COUNTIFS([End Date]:[End Date], AND(@cell > TODAY(+28), @cell <= TODAY(+35)), [Indent Level]:[Indent Level], @cell = 3)
Adjusting the number sin the TODAY function as needed for each week. Then the total Summary field would just be adding all of the individual weeks together.
Answers

Have you looked into using a separate sheet and formulas with cross sheet references?

No I haven't. I'm pondering what that workflow would look like. Could you provide a narrative of what you are thinking?
Thanks!

It is going to depend on exactly what you are looking for. It almost seems as if your formula is going to output either a 1 or a 0 on each row and then the 1 will move from right to left across the columns as the "deadline" approaches. Then the very top row has the totals. Is it just those totals you are interested in?

Primarily, yes.

Ok. I would set up a sheet that has the numbers 1  8 in rows 2  9 in the Primary Column. In Row 1 of the Primary Column enter "Total" and indent the rest of the numbers underneath. In the next column over (using the appropriate steps for creating cross sheet references, we would use a column formula such as this:
=IF([Primary Column]@row = "Total", SUM(CHILDREN()), COUNTIFS({Main Sheet Date Column}, AND(@cell>= TODAY(([Primary Column]@row  1) * 7), @cell<= TODAY([Primary Column]@row * 7)), {Main Sheet Calc If Done Column}, @cell = 0))

I THINK I'm with you on this. But the calculations that produce the sums that I need are done on each CHILD row in the Schedule sheet. Not sure how to pull in that information with all the formulas being in the Schedule sheet.

I'm not sure I follow. What calculations are you summing on each of the child rows?

Sorry, I'll try to clarify here:
Each Module has several Assets and each Asset has several Tasks associated with it. That's the basic hierarchy of the sheet:
Module
Asset
Tasks
Due Dates for each Task that roll up to the due date for the Asset. I calculate how many weeks out the Task is using a formula like this for each TASK row:
=COUNTIFS([Calc if Done]@row, =0, [End Date]@row, AND(@cell > TODAY(+28), @cell <= TODAY(+35)))
Using different ranges for TODAY() to determine how many weeks out the task is due.
I then sum the Tasks (as well as the number of Assets (different pic) that are due in each upcoming week. Here are the sums that I report off of, located on the first row of the sheet.
I use this formula on the first for for each Task and Asset. The Indent Level grabs the correct row.
=SUMIF([Indent Level]:[Indent Level], =3, [3 Weeks Tasks]:[3 Weeks Tasks])
So basically, I'm looking for a way to get these details using more Summary fields than columns in the Sheet itself. I have numerous Sheets with this data being reported on and numerous Assets and Tasks as well as other Row calculations that I'm summing up for reports.
Any help on how to reduce the number of Columns and still get my sums would be greatly appreciated.
Hope this helps you to understand my current method better.
Thanks!

OK. If you want to use Sheet Summary fields instead, you would set up a summary field for each week and then use a COUNTIFS such as
=COUNTIFS([End Date]:[End Date], AND(@cell > TODAY(+28), @cell <= TODAY(+35)), [Indent Level]:[Indent Level], @cell = 3)
Adjusting the number sin the TODAY function as needed for each week. Then the total Summary field would just be adding all of the individual weeks together.

Oh my!!! So simple a solution that I just didn't think of! That works!
Thanks so much Paul!

On last formula question:
In order to evaluation on Indent Level 2 for a specific date range if there are ANY of the following entries in a specific column I am trying this formula. What am I doing wrong?
=COUNTIFS([End Date]:[End Date], AND(@cell > TODAY(),@cell <= TODAY(+7), AND(OR([Task Owner],@cell = "[email protected]", [Task Owner], @cell = "[email protected]", [Task Owner], @cell = "[email protected]", [Task Owner], @cell = "AMT Distribution List")))), [Indent Level]:[Indent Level], @cell = 3)

Take a look at this one... You don't need to include the AND function for extra range/criteria sets as it is already implied. The OR function would work similar to the AND function in the Date range/criteria set in that we establish the range and then the criteria is going to be the OR function with @cell references.
=COUNTIFS([End Date]:[End Date], AND(@cell > TODAY(),@cell <= TODAY(+7), [Task Owner]:[Task Owner], OR(@cell = "[email protected]", @cell = "[email protected]", @cell = "[email protected]", @cell = "AMT Distribution List"), [Indent Level]:[Indent Level], @cell = 2)

Ok, that makes sense. I'm getting an #Invalid Data type result using this formula. The Task Owner column is a Contact List, thought that might be the issue but didn't help to change that to Text/Number. I updated the Indent Level cell to 3 but the rest looks ok to me.

Double check that the End Date column is in fact still set as a date type column.

It is. Still evaluating what the issue might be. Appreciate your thoughts.
Help Article Resources
Categories
Check out the Formula Handbook template!