COUNTIFS Error

[arooney88]

So I recently upgraded to Excel 2010 and I'm using this formula in column A

=COUNTIFS('[Ward NOD Database.xlsm]Ward'!F4:F2383, "No", '[Ward NOD Database.xlsm]Ward'!$C$4:$C$2383, "106")

Which returns the value "8". I'm looking up info on a seperate workbook that I have open in the same Excel instance and that value 8 is correct if I go and filter to double check.

The issue I'm having is that I need to copy this formula into 9 other columns to the right so the reference moves to G4:G2383, H4:H2383, etc. When I do this it lists 8 as the returned values in all of those cells. When I go and filter to double check, that's not the right number it should be returning. I clicked in the formula manually though and hit 'enter' and then it calculated the correct total??

I'm wondering if there is an issue with 2010 and COUNTIFS or if I'm saving the file in a weird way or something? I'm saving as a .xlsm macro enabled workbook.

Anyone had this same issue before??

 

[Jonmo1]

Sounds like your Calculation is set on Manual on the Formulas Tab.

 

[arooney88]

How would I change this to automatic? I don't think I changed it to ever be manual??

 

[Jonmo1]

Click the "Calculations Options" button on the Formula Tab.

 

[arooney88]

Nevermind I see it!

I'm going to see if this fixes my issue

 

[arooney88]

THANKS SO MUCH. I didn't even think about this at all.

 

[Jonmo1]

Glad to help, thanks for the feedback.