Issue with SUMIFS/COUNTIFS formula throwing VALUE errors...

Goose306

Board Regular
Joined
Sep 26, 2014
Messages
52
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:

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:

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
In Sumifs and Countifs, the Criteria Ranges must be the same dimentions as the Sum Range..

Your SumRange is covering rows 4 to 400, but 2 of your criteria ranges are going rows 4 to 1001

The either ALL need to be rows 4:400, or all 4:1001
 

James02

Board Regular
Joined
Nov 6, 2014
Messages
108
If any value in the table rage is an error or text, it could cause #VALUE. Another idea is to just try a SUMIF with column M and each IF statement. So sum column M using Reaudit Review column H. Then try the seperate ones to see which column is screwing it up.
 

Goose306

Board Regular
Joined
Sep 26, 2014
Messages
52
In Sumifs and Countifs, the Criteria Ranges must be the same dimentions as the Sum Range..

Your SumRange is covering rows 4 to 400, but 2 of your criteria ranges are going rows 4 to 1001

The either ALL need to be rows 4:400, or all 4:1001


I knew it had to be something stupid... spend all day in Excel working on formulas and you always miss the obvious one at the end of the day. Thought coming in fresh this morning would help, but here I am. :rolleyes: That also explains why it was always failing on the last step.

Seems like that took care of the issue, thanks!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,191
Messages
5,623,291
Members
415,963
Latest member
PatrickDurning

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top