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.
Best Answer
-
@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
Answers
-
@Fboivin I think your formula should be, =SUMIF(SSRS:SSRS,"Customer Meeting", [PM1]:[PM5]), just change what's in the quotes per summary
-
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
-
EDIT: I am looking at this again.
-
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.
-
@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
-
Thank you Peggy that works for what I need.
-
@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.
-
Thanks @Eric Law
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 454 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!