Strange #VALUE Error

Occasional Contributor

Hi, I'm building a spreadsheet which uses a lot of COUNTIF Formulas. The workbook has one sheet with formulas, one sheet with data for year 1, and one sheet with data for year 2.

When I run a formula based on Year 2, it works great. You will see I'm using Column Names in my formula.

However, when I run an equivalent formula on Year 1, I get an error when I add in one of the COUNTIF criteria. Yet running this error prone COUNTIF formula on it's own does not give me an error. i.e. the error only occurs when I nest this formula in the COUNTIF with other criteria.
So this works on it's own:

This works (which excludes the above):

But when I next them together, I get an error. I can't get my head around it.

I've never heard of an error where certain COUNTIF criteria work fine apart but can't be combined. Can anyone help?!

In the attached I've highlighted 4 cells with formula issues like this in yellow. 2 are working (working from data in the Current Orders sheet), 2 aren't (working from data in the prev year Orders sheet)




5 Replies


The defined names don't have the same size.

Registration_Type_Prev_Yr and Sales_Method_Prev_Yr refer to rows 2:1048576, but Job_Function_PY refers to an entire column (i.e. 1:1048576). Modify Job_Function_PY to refer to rows 2:1048576 too.

@Hans Vogelaar this is true and thank-you for spotting! However, that's one of the things I tried adjusting and it didn't fix it sadly. So I don't think it can be the cause  


It works!


But you also have invalid named ranges:


Workbook attached below.

Amazing thank-you so much! How did you fix it out of interest?

Don't worry about those other references. I hadn't got as far as fixing them as I'd lost about 2 hours trying to fix this issue!
I just re-read your explanation from before and I understand now. Sorry, I misunderstood what you were saying before or I would have tested myself! Thank-you again!!!