SUMIFS Formula Help

Aaron Kennedy

I want a cell to count the total hours for different kinds of employees. The columns I have are:

Job #, Date, Employee name, Employee type, employee hours

I want to add up the total hours for each employee type, on the same sheet or another sheet, I just need help to add them up by type.

Michelle Choate 2

=SUMIFS([Employee Hours]:[Employee Hours], [Employee Type]:[Employee Type], "EMPLOYEE TYPE 1")

AKnight

Hi @Aaron Kennedy !

I suggest using the SUMIF formula for this ask. If you are just doing the formula on the same sheet your formula can look like the following:

=SUMIF([Employee Type]:[Employee Type], "Employee X", [Employee Hours]:[Employee Hours])

This formula is using Employee X as the employee type that you are calculating hours for. You will need to replace it for each employee type you plan on calculating hours for. You can store this formula in the sheet summary or at the top of the sheet in a sheet summary collapsible row.

You can also set up a rollup sheet (what I prefer to do) especially if you plan on pulling this information from multiple sheets.

If you plan on using data from outside sheets you will have to set up cross-sheet references (see link below) and change your range at the beginning of the formula [Employee Type]:[Employee Type] to your cross sheet reference. When pulling from multiple sheets I usually use the addition feature to get a total value which ends up looking like:

=SUMIF({Sheet 1 - Employee Type}, "Employee X", [Employee Hours]:[Employee Hours]) + SUMIF({Sheet 2 - Employee Type}, "Employee X", [Employee Hours]:[Employee Hours])

Additional help resources:

SUMIF help: https://help.smartsheet.com/function/sumif

Cross-sheet referencing help: https://help.smartsheet.com/articles/2482644-create-cross-sheet-references

Hope this helps!

-Ashley