Formula to Total rows

I have Rows with names of People

Then I have columns that show projects / Activity names

I am trying to create a summary field that will sum all the time spent on two categories by all the people. Either "Customer Meeting" or "Testing". The rows those are listed in might not always be the same. For example one month they may be in Row 2 and 45 and another Month it may be in row 245 and 250.

The list of Category names start in Row 2 and go all the way through Row 250

I've tried several iterations but can't seem to quite get my syntax to work.

This was the last one I was trying but it comes back as unparseable

=SUMIF(SSRS2:SSRS250, <>"Customer Meeting", [PM1]@row:[PM5]@row)

Below is a screen shot of the sheet.

Find more posts tagged with

Accepted answers

Peggy Parchert

@fboivin -

Hello. You could create a helper column and sum the rows and then create a Sheet Summary field to break down each SSRS.

The helper column, Total, would have a column formula: =IF(SSRS@row = "Customer Meeting", SUM([PM1]@row:[PM5]@row), IF(SSRS@row = "Testing", SUM([PM1]@row:[PM5]@row), 0))

The two Sheet Summary fields would have:

Customer Testing - =SUMIF(SSRS:SSRS, "Customer Meeting", Total:Total)

Testing - =SUMIF(SSRS:SSRS, "Testing", Total:Total)

Another option - hope it helps.

Thanks -Peggy

All comments

Eric Law

@Fboivin I think your formula should be, =SUMIF(SSRS:SSRS,"Customer Meeting", [PM1]:[PM5]), just change what's in the quotes per summary

Fboivin

That gives an Invalid Column Value error.

I thought maybe because Row 1 has Names in instead of numbers.

So I tried this instead so it would only add starting at Row 2 where the numbers are

=SUMIF(SSRS2:SSRS250, "Customer Meeting", [PM1]2:[PM5]250)

However that gives error of #Incorrect Argument Set

Eric Law

EDIT: I am looking at this again.

Fboivin

Hi Eric,

I did try that and it gives this error:

#Incorrect Argument Set

It doesn't seem to like that the sum range is across multiple columns. If i change it to just

=SUMIF(SSRS2:SSRS250, "Customer Meeting", [PM1]2:[PM1]250)

That works fine, however that would force me to create that same formula for every column and then add them together.

Peggy Parchert

@fboivin -

Hello. You could create a helper column and sum the rows and then create a Sheet Summary field to break down each SSRS.

The helper column, Total, would have a column formula: =IF(SSRS@row = "Customer Meeting", SUM([PM1]@row:[PM5]@row), IF(SSRS@row = "Testing", SUM([PM1]@row:[PM5]@row), 0))

The two Sheet Summary fields would have:

Customer Testing - =SUMIF(SSRS:SSRS, "Customer Meeting", Total:Total)

Testing - =SUMIF(SSRS:SSRS, "Testing", Total:Total)

Another option - hope it helps.

Thanks -Peggy

Fboivin

Thank you Peggy that works for what I need.

Eric Law

@Peggy Parchert That's such a great simple solution with a helper total column. It is weird that SUMIF can't sum multiple columns with 1 criteria column.

Peggy Parchert

Thanks @Eric Law

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.