Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Sumif help
Hi,
I am trying to show a rolling 6 months resource allocation matrix, so that I am able to include the resource allocation figure on the dashboard and need some help with the formula that will be required.
Tried the formula in the snap shot, but it does not seem to work
=SUMIFS([Resource Duration]7:[Resource Duration]23), [Finish]7:[Finish]23) = [Today 1]1 + 122)
Thanks heaps
Cathy
Comments
-
Hi Cathy,
Try this formula:
=SUMIFS([Resource Duration]7:[Resource Duration]23, Finish7:Finish23, >TODAY(122))
You were missing the comma after the 'Finish' criteria range, and you may want to use '>' instead of '=' for the criteria depending on the results you're looking for.
You can also use the built-in TODAY function instead of needing a cell if you'd like.
Hope this helps.
-
Thanks Mark, but it still does not seem to work still getting #UNPARSEABLE
Sorry must had the formula in wrong, the one you sent through is working. I just need to do a little tweaking with it.
-
Hi Mark, is there a way that you are aware that the above formula would only look at the Children and provide a rolling Resourse duration total figure if the finish date was within the next 6 months?
Regards
Cathy
-
Hi Cathy,
I think I understand where you're going. See if this works:
=IF(COUNT(CHILDREN([Task Name]1)) > 0, SUMIFS(CHILDREN(Duration1), CHILDREN(Finish1), >TODAY(122)), "")
To break this down:
IF Statement - This is so you only sum on parent rows. You will sum all of the children for that parent. If the cell is a child, leave it blank (that's the "" at the end of the IF statement).
COUNT(CHILDREN([Task Name]1)) > 0 - This checks if you are on a parent row or not. You will need to change "Task Name" to whatever your primary column name is.
SUMIFS(CHILDREN(Duration1), CHILDREN(Finish1), >TODAY(122)) - This is basically the same formula as before.
If you are looking for a grand total, you could sum the entire Allocation column using this formula:
=SUM(Allocation:Allocation))
Hope that helps.
-
Thanks Mark I will give the above a go and let you know the out come.
Kind Regards
Cathy
