SUMIF/AVERAGEIF across rows not working
I'm trying to SUMIF and AVERAGEIF across rows in my sheet, and I keep getting an #INVALID VALUE error. My table of data includes columns across for each employee, and each row contains the employee's hours worked.
The screenshot below shows the helper row (row 3 in my sheet) that specifies whether each employee is a production employee (as opposed to an office employee) and the "Total" row (green bold row) which is a sum of each employee's weekly hours worked.
The formula I want to write should be very simple. "=SUMIF($[Emp 01]$3:$[Emp 50]$3,"Yes",[Emp 01]@row:[Emp 50]@row)" This formula essentially adds up all the cells in the current row where the value in the helper row (row 3) is equal to "Yes". However, that formula doesn't work.
I saw in another post on here that you have to string together the SUMIF functions if you want to SUMIF across several columns. However, the only single SUMIF formula I could get to work was the very first one, looking at only the very first [Emp 01] column. "=SUMIF([Emp 01]3,"Yes",[Emp 01]@row)" returns 1,282.07, but "=SUMIF([Emp 02]3,"Yes",[Emp 02]@row)" returns the #INVALID VALUE error.
I'm not sure why one formula would work, but the exact same formula looking at a different column wouldn't work, especially when both columns in question are formatted exactly the same.
Help, please!
Best Answers

The Row 4 formula in the [Emp 02] column is the same as the Row 4 formula in every other [Emp XX] column. (Row 4 is the green row in my screenshot above.)
[Emp 01] Row 4 formula: =IF([Emp 01]2 = "", "", SUM([Emp 01]$6:[Emp 01]$57))
[Emp 02] Row 4 formula: =IF([Emp 02]2 = "", "", SUM([Emp 02]$6:[Emp 02]$57))
[Emp 03] Row 4 formula: =IF([Emp 03]2 = "", "", SUM([Emp 03]$6:[Emp 03]$57))
...and etc. etc. etc.
All the columns are formatted exactly the same, as Text/Number columns.

And, wouldn't you know, (for gits and shiggles) when I tried the formula again just now to see if it would work, LO AND BEHOLD! It did. Both the SUMIF and AVERAGEIF formulas are now behaving as they should.
I have zero explanation for this. Maybe it was too late last night when I was working on this and smartsheet was tired and just *didn't wanna*? Or maybe the product enhancement request for this particular functionality was finally granted serendipitously between last night and today? Or MAYBE the trigger for the fix was posting this question in the community?! Or did @Paul Newcome press some magic button? (That has to be it.) Hmm. I guess we'll never know. (I do know that I didn't change anything in my sheet since posting this last night. Everything in my sheet is exactly how it was when the formula wouldn't work yesterday. Except that the rewritten formula works now.)
Ok, I'll stop asking questions now and simply *enjoy* the mysteriouslyrenewed functionality of the SUMIF and AVERAGEIF functions. Yay!
Thanks!

Sometimes (because of cookies and gremlins) sheets like to hold onto incorrect data on the backend. Once we got through ensuring data was accurate, my next suggestion was going to be to delete formula, log out, clear cookies and cache, log back in, retype formula. Sometimes that old data just kind of clears itself out over time as well (as it seems to be in your case).
Glad it is working for you now.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am  12:45pm!

Happy to help. 👍️
P.S. The log out/log in bit is typically one of the first things Support will suggest after you get the generic email telling you the proper syntax for whatever function you have mentioned doesn't work.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am  12:45pm!
Answers

That first one should be working. Have you looked at every cell referenced to see if that error is present in any cell? How exactly are the numbers populated?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am  12:45pm!

Hi Paul, thanks for responding!
Yes, every cell referenced is errorfree. The numbers in the green row are SUM formulas of the rows below. All cells in the entire sheet are errorfree.
Any ideas why the formula referencing the first column works, but the formula referencing the second column results in an error?

Double check the data in [Emp 02]@row. It is almost as if that is being read as text. What formula is in that particular cell?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am  12:45pm!

The Row 4 formula in the [Emp 02] column is the same as the Row 4 formula in every other [Emp XX] column. (Row 4 is the green row in my screenshot above.)
[Emp 01] Row 4 formula: =IF([Emp 01]2 = "", "", SUM([Emp 01]$6:[Emp 01]$57))
[Emp 02] Row 4 formula: =IF([Emp 02]2 = "", "", SUM([Emp 02]$6:[Emp 02]$57))
[Emp 03] Row 4 formula: =IF([Emp 03]2 = "", "", SUM([Emp 03]$6:[Emp 03]$57))
...and etc. etc. etc.
All the columns are formatted exactly the same, as Text/Number columns.

And, wouldn't you know, (for gits and shiggles) when I tried the formula again just now to see if it would work, LO AND BEHOLD! It did. Both the SUMIF and AVERAGEIF formulas are now behaving as they should.
I have zero explanation for this. Maybe it was too late last night when I was working on this and smartsheet was tired and just *didn't wanna*? Or maybe the product enhancement request for this particular functionality was finally granted serendipitously between last night and today? Or MAYBE the trigger for the fix was posting this question in the community?! Or did @Paul Newcome press some magic button? (That has to be it.) Hmm. I guess we'll never know. (I do know that I didn't change anything in my sheet since posting this last night. Everything in my sheet is exactly how it was when the formula wouldn't work yesterday. Except that the rewritten formula works now.)
Ok, I'll stop asking questions now and simply *enjoy* the mysteriouslyrenewed functionality of the SUMIF and AVERAGEIF functions. Yay!
Thanks!

Sometimes (because of cookies and gremlins) sheets like to hold onto incorrect data on the backend. Once we got through ensuring data was accurate, my next suggestion was going to be to delete formula, log out, clear cookies and cache, log back in, retype formula. Sometimes that old data just kind of clears itself out over time as well (as it seems to be in your case).
Glad it is working for you now.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am  12:45pm!

Aha! I like the "cookies and gremlins" answer! I appreciate your insight, thank you!

Happy to help. 👍️
P.S. The log out/log in bit is typically one of the first things Support will suggest after you get the generic email telling you the proper syntax for whatever function you have mentioned doesn't work.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am  12:45pm!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63K Get Help
 380 Global Discussions
 212 Industry Talk
 442 Announcements
 4.6K Ideas & Feature Requests
 140 Brandfolder
 129 Just for fun
 130 Community Job Board
 449 Show & Tell
 30 Member Spotlight
 1 SmartStories
 305 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!