# Sheet Summary Formula

✭✭

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.

Tags:

• ✭✭✭✭✭✭
edited 04/12/23

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

Ryan Sides

Come Say Hello!

• ✭✭

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])

• ✭✭✭✭✭✭

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. :)

Ryan Sides

Come Say Hello!

• ✭✭

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?

• ✭✭✭✭✭✭

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

Ryan Sides

Come Say Hello!

• ✭✭
edited 04/14/23

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

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

• ✭✭✭✭✭✭

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.

Ryan Sides

Come Say Hello!

• ✭✭

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?

• ✭✭✭✭✭✭

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)

Ryan Sides

Come Say Hello!

• ✭✭

Thanks Ryan!

• ✭✭✭✭✭✭

My pleasure!

Ryan Sides

Come Say Hello!

• ✭✭

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!

• ✭✭✭✭✭✭

Sure thing! Just use an if statement in there:

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

Ryan Sides

Come Say Hello!

• ✭✭

You are a genius! Appreciate your help once again!

• ✭✭✭✭✭✭

My pleasure! Happy to help

Ryan Sides