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
Check out the Formula Handbook template!