Add column values where another column meets a criteria

Remy V

Hello,

I'm trying to add up the values in "Hours per week" where the "Employee name" does not equal "OPEN". Where am I going wrong?

Any help appreciated!

Mark.poole

@Remy V

As everything is on the same sheet. You don't need to do an index collect.

=Sumifs([Hours Per Week]1:[Hours Per Week]10,[Employee Name]1:[Employee Name]10<>"Open")

Mark.poole

I noticed a slight syntax error in the formula I gave. This new formula fixes it.

=Sumifs([Hours Per Week]1:[Hours Per Week]10,[Employee Name]1:[Employee Name]10,<>"Open")

Remy V

@Mark.poole is there a way to do this without noting the end row? I want it to sum all the above columns that match the criteria even if extra rows are added.

Mark.poole

@Remy V

The problem is if you don't notate the end row it will try to count itself which is what's causing the error.

IF you do a hierarchy. Making the rows you want to sum as the children. Move the master total to the top of the names. Indent all the names to be children of Master total. Then you can do

=SUMIFS(CHILDREN([Hours per week]1), CHILDREN([Employee Name]1), "Open")