I need to Sum a column from a reference sheet and display the number in my primary sheet

Jewelle W

I am attempting to build a column formula that generates the sum of hours for a given phase (task). This is an interaction of our Main project grid and the Timesheet grid (it is one of the advanced reports Resource management offers) from Resource Management.

Our main project should display the number of hours incurred for that specific phase in its own row. Ie Task 2 should be a sum of the incurred hours column that have with "Task 2" in the phase name. Same for Task 3 and so on.

Main Project Sheet:

Here are some of the formulas I have tried but haven't quite worked for me:

1. =COUNTIF({Incurred Hours}, {Phase Name} = [Phase Name]@row ) – This just gave me 0 (which isn’t correct based on my sum of the phase’s incurred hours)

2. I also did this formula based around IF: =IF([Budget:]@row > 0, SUMIF({Incurred Hours}, {Phase Name} = [Phase Name]@row ), "") - No error message but this also gave me 0 (which is incorrect)

3. I think I am using the wrong formula syntax but here is the most recent formula I did that gave me "unparsable" as the return: =SUMIFS({TS WIP Incurred Hours}, {TS WIP Project Name} HAS([Task Name]1), {Timesheet for WIP Budget - Hours Data Range 2} =[Phase Name]@row )

Finally I want this to be a formula that works no matter the project so that I can set a template that I can reuse for future projects without re-entering the formula.

I am missing something, but I am not sure what. Please help! Thanks

jmyzk_cloudsmart_jp

Hi @Jewelle W

You can use the SUMIFS function.

=SUMIFS({TS WIP Incurred Hours}, {TS WIP : Project Name}, [Project Name]@row , {TS WIP : Phase Name}, [Phase Name]@row , {TS WIP : Task Name}, [Task Name]@row )

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

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

By the way, the correct format for COUNTIF(S) and SUMIF in your formula is as shown below.

=COUNTIF({Incurred Hours}, {Phase Name} = [Phase Name]@row )

=COUNTIF({Incurred Hours},>0, {Phase Name} , [Phase Name]@row )

COUNTIFS(range1, criterion1, [range2, criterion2, ...])

=IF([Budget:]@row > 0, SUMIF({Incurred Hours}, {Phase Name} = [Phase Name]@row ), "")

  =IF([Budget:]@row > 0, SUMIF( {Phase Name}, [Phase Name]@row, ({Incurred Hours} ), "")

SUMIF(range, criterion, [sum_range])