SUMIF/AVERAGEIF across rows not working

Options
Amy Foeller
Amy Foeller ✭✭✭✭

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.

User: "image.png"

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!

Tags:

Best Answers

  • Amy Foeller
    Amy Foeller ✭✭✭✭
    Answer βœ“

    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!

  • Paul Newcome
    Paul Newcome Community Champion
    Answer βœ“

    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.

  • Paul Newcome
    Paul Newcome Community Champion
    Answer βœ“

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!