Need Formula Help

Jonna Critchley

Hi There! In need of a formula to that would sum time based on these columns:

Resource Name

Group Name

Week Ending

This is my time repository, I need to sum each resource's time based on Group Name and Week Ending (which is actually the Date Column, my Week Ending column has to be a text column) on another Sheet.

https://app.smartsheet.com/b/publish?EQBCT=b03ffd3f87e440cea615d3d7e9ce8eae

Any assistance would be GREAT!!!

Bassam Khalil

Hi @Jonna Critchley 

Hope you are fine, you need to count in the same sheet or on another sheet?

Andrée Starå

Hi @Jonna Critchley

I hope you're well and safe!

Can you share both sheets, the sheet already added and the other where you want the SUM to be?

Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Jonna Critchley

@Andrée Starå @Bassam Khalil

Thank you both for responding!

Here is a view of the Hours Summary sheet: https://app.smartsheet.com/b/publish?EQBCT=c81aa136c65341a58032487775afb714

Previously (as in last week), the hours summary ran off of a formula from each Resources Weekly Report SHEET. We've just this week moved their time reporting to Dynamic View - hence why I'm asking for a new formula 😉.

As you can see, under each month is a formula:

To get this 17.25 for March 2021, I use:

=SUMIFS({Weekly Report - Commercial Facilitation Range 1}, {Weekly Report - Commercial Facilitation Range 2}, ="Actualize Consulting", {Weekly Report - Commercial Facilitation Range 3}, >=DATE(2021, 3, 1), {Weekly Report - Commercial Facilitation Range 3}, <=DATE(2021, 3, 31))

Here is what the sheet it's pulling off of looks like: https://app.smartsheet.com/b/publish?EQBCT=1cad00cac018407b9baf7ab6a5f1148a

Range 1 = Time Column

Range 2 = Resource Name

Range 3 = Week Ending

I now have one sheet that captures all time, instead of individual sheets, so I assume my formula gets even more complicated.

In the case of Commercial Facilitation, this sheet https://app.smartsheet.com/b/publish?EQBCT=1cad00cac018407b9baf7ab6a5f1148a is now replaced by Row 20, Row 94, Rows 168 and Rows 242 (just for August) on this sheet: https://app.smartsheet.com/b/publish?EQBCT=b03ffd3f87e440cea615d3d7e9ce8eae

In summary, I need a formula that says: John totaled X amount of hours for the month of August (and then each month after)

REALLY APPRECIATE THE HELP ON THIS!

Jonna Critchley

@Andrée Starå @Bassam Khalil

My apologies, the "In Summary" up there should have said:

I need a formula that says: John totaled X amount of hours for the month of August (and then each month after) for CMISMO.

Additionally, here is a better screenshot than the one above: