Sheet Summary Formula

FlyFlip

Hi Team!

I'm hoping to get some help on Sheet Summary formulas.

As you can see per my screenshot below, we have 2 projects. I'd like to do 2 things on the sheet summary:

1. Be able to filter by project name.
2. Once I filter by project name, I'd like to see see the total amount, the total amount due, and the total paid for that specific project. I already have formulas in the sheet summary to calculate the total amount, total due and total paid for the entire sheet but I'd like to break it down by project as well. If the sheet summary is not the ideal option for this, I'm open to all ideas.

Thanks ahead of time for your help!

Ryan Sides

Hey Fly, you can do it in the sheet summary:

1) add a dropdown field in your summary. Put Project 1 and Project 2 in it.

2) add a helper column to detect the children of your new field

3) Then change your sheet summary formulas to look at the helper column to use in your SUMIFS formulas

This is an OK place to do what you're looking for, for just these two projects. But if you have more projects later you might want to consider creating another sheet that totals these up for all projects at the same time. You can then display all of those numbers on a dashboard, etc without having to change the dropdown.

Does that make sense?

Here's a video that explains it a bit better: https://youtu.be/0WCojlLpAl0

FlyFlip

Thanks Ryan. I'll give that a try.

In the meantime, are you able to help me with a formula calculating the "Amount Due" and "Amount Paid" by parent row?

Right now I have formulas (below) calculating the "Amount Due" and "Amount Paid" for the entire sheet but not by parent row.

=SUMIF([Paid Date]:[Paid Date], ISBLANK(@cell), [Total Amount]:[Total Amount])

=SUMIF([Paid Date]:[Paid Date], ISDATE(@cell), [Total Amount]:[Total Amount])

Ryan Sides

Amount Due:

=SUMIFS(CHILDREN([Total Amount]@row), CHILDREN([Paid Date]@row), ISBLANK(@cell))

Amount Paid:

=SUMIFS(CHILDREN([Total Amount]@row), CHILDREN([Paid Date]@row), ISDATE(@cell))

Please let me know if that works for you. :)

FlyFlip

You are the man!!!

I'm sorry to ask you for more help, but I'm also trying to create formulas that show the amount that is due within 1-30 days, 31-60 days, and 61-90 days from "today". Basically like an aging report. Is that possible?

Ryan Sides

haha! Glad it's working.

Does it need to be for each parent or in total for your sheet? Also, where are you storing the due date?

FlyFlip

I was thinking for the whole sheet and putting it in the sheets summary.

Also, is there a formula to sum only parent rows?

Ryan Sides

 where are you storing the due date?

And yes, you can sum only parent rows. You can use a helper column that counts the DESCENDENTS of the row. If the count is greater than 0, then you know it's a parent row.

FlyFlip

I have a column that is "due date"

Regarding the formula to only sum parent rows, I'd like the sum of ALL parent rows on the sheet. Is there formula for that?

Ryan Sides

Yes! Add a column named "Descendants". In that column, create a column formula:

=COUNT(DESCENDANTS([Molle Project Number]@row))

In your Sheet Summary create a field, you can call it "Sum of All Parent Rows", add the following formula to it:

=SUMIFS([Amount Due]:[Amount Due], Descendants:Descendants, >0)

FlyFlip

Thanks Ryan!

Ryan Sides

My pleasure!

FlyFlip

Hi Ryan!

Hoping you can help me with quick formula. I have 3 columns. Task Time, Start Time and End Time.

I'd like the Task Time to concatenate the Start Time and End Time which I was able to do with the formula below:

=[Start Time]@row + " to " + [End Time]@row

BUT there are some situations where I don't have an End Time. In those cases, I'd like the Task Time to just say the Start Time. Right now it ends up saying "7:00am to" which doesn't look good on the calendar without an end time.

Thanks!

Ryan Sides

Sure thing! Just use an if statement in there:

=[Start Time]@row + if(isblank([End Time]@row), "", " to " + [End Time]@row)

FlyFlip

You are a genius! Appreciate your help once again!

Ryan Sides

My pleasure! Happy to help