Hey all,
So I have a few formulas that run in the background of a workbook that is used at my work and keeps track of progress/amount of work done by different people in my department. I am trying to make some tweaks/additional adjustments to the formula, and it's getting hung up but I can't figure out why as the formula looks correct to me, and when I step through the calculation in the error tracking all the correct values come up, then it errors for no reason I can determine.
I'm sure it's something obvious I'm just missing. Here is the SUMIFS formula:
Where Reaudit Review tab column M is the column being summed, Reaudit Review tab column H is a date column, Reaudit Review tab column B is the worker's name. Work tracker tab cell C5 is the worker name, D5 is the beginning of the timeframe being compared, and E5 is the end of the timeframe being compared.
When I step through this formula it shows all of the correct data being filled as expected (Worker name is filled, dates are pulled across) then on the final step it kicks out a VALUE error.
Any ideas? The last thing I added was the date range check, it worked prior to then. However, since this data is showing up when stepping through the formula I don't see how it is causing the error. I am also using the same date range IF statements in similar SUMIFS formulas on the Work Tracker tab and it works fine.
PS - here is the COUNTIF formula that is throwing the same error. It works off more or less similar functions and goes to the same steps, it just has an additional IF check and is looking for different values:
So I have a few formulas that run in the background of a workbook that is used at my work and keeps track of progress/amount of work done by different people in my department. I am trying to make some tweaks/additional adjustments to the formula, and it's getting hung up but I can't figure out why as the formula looks correct to me, and when I step through the calculation in the error tracking all the correct values come up, then it errors for no reason I can determine.
I'm sure it's something obvious I'm just missing. Here is the SUMIFS formula:
Code:
=SUMIFS('Reaudit Review'!$M$4:$M$400,'Reaudit Review'!$H$4:$H$1001,">="&'Work Tracker'!$D$5,'Reaudit Review'!$H$4:$H$1001,"<="&'Work Tracker'!$E$5,'Reaudit Review'!$B$4:$B$400,'Work Tracker'!$C$5)Where Reaudit Review tab column M is the column being summed, Reaudit Review tab column H is a date column, Reaudit Review tab column B is the worker's name. Work tracker tab cell C5 is the worker name, D5 is the beginning of the timeframe being compared, and E5 is the end of the timeframe being compared.
When I step through this formula it shows all of the correct data being filled as expected (Worker name is filled, dates are pulled across) then on the final step it kicks out a VALUE error.
Any ideas? The last thing I added was the date range check, it worked prior to then. However, since this data is showing up when stepping through the formula I don't see how it is causing the error. I am also using the same date range IF statements in similar SUMIFS formulas on the Work Tracker tab and it works fine.
PS - here is the COUNTIF formula that is throwing the same error. It works off more or less similar functions and goes to the same steps, it just has an additional IF check and is looking for different values:
Code:
=COUNTIFS('Outlier Summary Review'!$B$4:$B$400,'Work Tracker'!$C$5,'Outlier Summary Review'!$I$4:$I$1001,">="&'Work Tracker'!$D$5,'Outlier Summary Review'!$I$4:I$1001,"<="&'Work Tracker'!$E$5,'Outlier Summary Review'!$P$4:$P$400,K4)
Last edited:
That also explains why it was always failing on the last step.
