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 re-written formula works now.)
Ok, I'll stop asking questions now and simply *enjoy* the mysteriously-renewed functionality of the SUMIF and AVERAGEIF functions. Yay!
Thanks!
-
Sometimes (because of cookies and gremlins) sheets like to hold onto incorrect data on the back-end. 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.
-
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.
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?
-
Hi Paul, thanks for responding!
Yes, every cell referenced is error-free. The numbers in the green row are SUM formulas of the rows below. All cells in the entire sheet are error-free.
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?
-
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 re-written formula works now.)
Ok, I'll stop asking questions now and simply *enjoy* the mysteriously-renewed functionality of the SUMIF and AVERAGEIF functions. Yay!
Thanks!
-
Sometimes (because of cookies and gremlins) sheets like to hold onto incorrect data on the back-end. 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.
-
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.
Help Article Resources
Categories
Check out the Formula Handbook template!
