Sumif help

Cathy Fraser

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

 

 

MarkLindsey

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.

 

Cathy Fraser

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.

 

 

Cathy Fraser

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

MarkLindsey

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.

Cathy Fraser

Thanks Mark I will give the above a go and let you know the out come. 

 

Kind Regards

Cathy