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