Multiple SUM IFS with same criteria

I'm trying to add up staff hours when the staff assigned could fall into any of 3 categories. How can I sum them all together?

This is how I've started but getting "#unparseable"

=SUMIFS([Staff #1]@row = "Bethany XXX", [Staff 1 Calculated Hours]@row +SUMIFS([Staff #2]@row = "Bethany XXX", [Staff 2 Calculated Hours]@row +SUMIFS([Staff #3]@row = "Bethany XXX", [Staff 3 Calculated Hours]@row )))

Example:

Find more posts tagged with

Accepted answers

All comments

Paul Newcome

It looks like you need to close out each of the SUMIFS functions individually.

=SUMIFS(..........) + SUMIFS(..........) + SUMIFS(..........)

It also looks like each of the SUMIFS have improper syntax.

=SUMIFS(range to sum, 1st criteria range, 1st criteria)

Anne77

Thanks Paul, I made the adjustment but still get "#unparseable"

Paul Newcome

Can you copy/paste the new formula to here so I can see exactly what you have?

Anne77

=SUMIFS([Staff #1]@row ="Bethany Dodd", [Staff 1 Calculated Hours]@row) +SUMIFS([Staff #2]@row ="Bethany Dodd", [Staff 2 Calculated Hours]@row) +SUMIFS([Staff #3]@row ="Bethany Dodd", [Staff 3 Calculated Hours]@row)

Paul Newcome

The syntax for each individual SUMIFS is still incorrect.

SUMIFS(range to sum, 1st criteria range, 1st criteria)

SUMIFS([Staff 1 Calculated Hours]@row, [Staff #1]@row, ="Bethany Dodd")

Anne77

Still getting #unparseable

=SUMIFS([Staff 1 Calculated Hours]@row, [Staff #1]@row, ="Bethany Dodd") +SUMIFS([Staff 2 Calculated Hours]@row, [Staff #2]@row, ="Bethany Dodd") +SUMIFS([Staff 3 Calculated Hours]@row, [Staff #3]@row, ="Bethany Dodd")

Hollie Green

Can you post the updated formula if it is still giving a #unparseable something is still incorrect in the formula. Could be the order, a comma, a bracket, a parenthesis, or a typo.

Anne77

Updated formula is directly above. Thanks Hollie!

Paul Newcome

I don't see any issues with the formula that is posted. Are you able to provide a screenshot of the formula actually in the sheet similar to the screenshot below (including the column headers)? Color coding may help give us a clue.

Anne77

Paul Newcome

It looks like there may be an issue with the column names. Try retyping the formula, but click on the cell being referenced instead of typing out the column name.

Anne77

If I do that it only references that one cell in that one row - I would like it to search the entire column for every instance of Bethany and then look at the Hours column to Sum the amount for the corresponding row for every time she is referenced (sometimes in Staff #2, sometimes in Staff 3#)

Anne77

For the record, here is how I finally got it to work. You need to include the full column instead of the row:

Paul Newcome

It should have worked with the @row references. I didn't mention the entire column because I thought you were trying to get it to sum on each row. But yes. To sum up an entire column you have to reference the entire column. Glad you were able to get it sorted.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.