Strange #VALUE Error

%3CLINGO-SUB%20id%3D%22lingo-sub-3031512%22%20slang%3D%22en-US%22%3EStrange%20%23VALUE%20Error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3031512%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20I'm%20building%20a%20spreadsheet%20which%20uses%20a%20lot%20of%20COUNTIF%20Formulas.%20The%20workbook%20has%20one%20sheet%20with%20formulas%2C%20one%20sheet%20with%20data%20for%20year%201%2C%20and%20one%20sheet%20with%20data%20for%20year%202.%3CBR%20%2F%3E%3CBR%20%2F%3EWhen%20I%20run%20a%20formula%20based%20on%20Year%202%2C%20it%20works%20great.%20You%20will%20see%20I'm%20using%20Column%20Names%20in%20my%20formula.%3CBR%20%2F%3E%3DCOUNTIFS(Registration_Type%2C%22Delegate%22%2CSales_Method%2C%22%22%2CJob_Function%2C%22*FE501*%22%2COrder_Status%2C%22%26lt%3B%26gt%3BCancelled%22%2CDel_Rev___Office_Currency%2C%22%26gt%3B0%22%2CProvisional%3F%2C%22N%22)%3CBR%20%2F%3E%3CBR%20%2F%3EHowever%2C%20when%20I%20run%20an%20equivalent%20formula%20on%20Year%201%2C%20I%20get%20an%20error%20when%20I%20add%20in%20one%20of%20the%20COUNTIF%20criteria.%20Yet%20running%20this%20error%20prone%20COUNTIF%20formula%20on%20it's%20own%20does%20not%20give%20me%20an%20error.%20i.e.%20the%20error%20only%20occurs%20when%20I%20nest%20this%20formula%20in%20the%20COUNTIF%20with%20other%20criteria.%3CBR%20%2F%3ESo%20this%20works%20on%20it's%20own%3A%3C%2FP%3E%3CP%3E%3DCOUNTIF(Job_Function_PY%2C%22%26lt%3B%26gt%3B*FE501*%22)%3CBR%20%2F%3EThis%20works%20(which%20excludes%20the%20above)%3A%3C%2FP%3E%3CP%3E%3DCOUNTIFS(Registration_Type_Prev_Yr%2C%22Delegate%22%2CSales_Method_Prev_Yr%2C%22%22%2COrder_Status_Prev_Yr%2C%22%26lt%3B%26gt%3BCancelled%22%2CDel_Rev___Office_Currency_Prev_Yr%2C%22%26gt%3B0%22)%3CBR%20%2F%3EBut%20when%20I%20next%20them%20together%2C%20I%20get%20an%20error.%20I%20can't%20get%20my%20head%20around%20it.%3CBR%20%2F%3E%3DCOUNTIFS(Registration_Type_Prev_Yr%2C%22Delegate%22%2CSales_Method_Prev_Yr%2C%22%22%2COrder_Status_Prev_Yr%2C%22%26lt%3B%26gt%3BCancelled%22%2CDel_Rev___Office_Currency_Prev_Yr%2C%22%26gt%3B0%22%2CJob_Function_PY%2C%22%26lt%3B%26gt%3B*FE501*%22)%3CBR%20%2F%3E%3CBR%20%2F%3EI've%20never%20heard%20of%20an%20error%20where%20certain%20COUNTIF%20criteria%20work%20fine%20apart%20but%20can't%20be%20combined.%20Can%20anyone%20help%3F!%3CBR%20%2F%3E%3CBR%20%2F%3EIn%20the%20attached%20I've%20highlighted%204%20cells%20with%20formula%20issues%20like%20this%20in%20yellow.%202%20are%20working%20(working%20from%20data%20in%20the%20Current%20Orders%20sheet)%2C%202%20aren't%20(working%20from%20data%20in%20the%20prev%20year%20Orders%20sheet)%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWill%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3031512%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3031722%22%20slang%3D%22en-US%22%3ERe%3A%20Strange%20%23VALUE%20Error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3031722%22%20slang%3D%22en-US%22%3EI%20just%20re-read%20your%20explanation%20from%20before%20and%20I%20understand%20now.%20Sorry%2C%20I%20misunderstood%20what%20you%20were%20saying%20before%20or%20I%20would%20have%20tested%20myself!%20Thank-you%20again!!!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3031712%22%20slang%3D%22en-US%22%3ERe%3A%20Strange%20%23VALUE%20Error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3031712%22%20slang%3D%22en-US%22%3EAmazing%20thank-you%20so%20much!%20How%20did%20you%20fix%20it%20out%20of%20interest%3F%3CBR%20%2F%3E%3CBR%20%2F%3EDon't%20worry%20about%20those%20other%20references.%20I%20hadn't%20got%20as%20far%20as%20fixing%20them%20as%20I'd%20lost%20about%202%20hours%20trying%20to%20fix%20this%20issue!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3031691%22%20slang%3D%22en-US%22%3ERe%3A%20Strange%20%23VALUE%20Error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3031691%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1241063%22%20target%3D%22_blank%22%3E%40WillWWBR%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20works!%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22S0972.png%22%20style%3D%22width%3A%20829px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F331926i1CEE1C87035218FD%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22S0972.png%22%20alt%3D%22S0972.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EBut%20you%20also%20have%20invalid%20named%20ranges%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22S0973.png%22%20style%3D%22width%3A%20552px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F331927i99E1A7396BF390DF%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22S0973.png%22%20alt%3D%22S0973.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EWorkbook%20attached%20below.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3031643%22%20slang%3D%22en-US%22%3ERe%3A%20Strange%20%23VALUE%20Error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3031643%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3Bthis%20is%20true%20and%20thank-you%20for%20spotting!%20However%2C%20that's%20one%20of%20the%20things%20I%20tried%20adjusting%20and%20it%20didn't%20fix%20it%20sadly.%20So%20I%20don't%20think%20it%20can%20be%20the%20cause%20%3CLI-EMOJI%20id%3D%22lia_disappointed-face%22%20title%3D%22%3Adisappointed_face%3A%22%3E%3C%2FLI-EMOJI%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3031601%22%20slang%3D%22en-US%22%3ERe%3A%20Strange%20%23VALUE%20Error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3031601%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1241063%22%20target%3D%22_blank%22%3E%40WillWWBR%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20defined%20names%20don't%20have%20the%20same%20size.%3C%2FP%3E%0A%3CP%3ERegistration_Type_Prev_Yr%20and%20Sales_Method_Prev_Yr%20refer%20to%20rows%202%3A1048576%2C%20but%20Job_Function_PY%20refers%20to%20an%20entire%20column%20(i.e.%201%3A1048576).%20Modify%20Job_Function_PY%20to%20refer%20to%20rows%202%3A1048576%20too.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New 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.
=COUNTIFS(Registration_Type,"Delegate",Sales_Method,"",Job_Function,"*FE501*",Order_Status,"<>Cancelled",Del_Rev___Office_Currency,">0",Provisional?,"N")

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:

=COUNTIF(Job_Function_PY,"<>*FE501*")
This works (which excludes the above):

=COUNTIFS(Registration_Type_Prev_Yr,"Delegate",Sales_Method_Prev_Yr,"",Order_Status_Prev_Yr,"<>Cancelled",Del_Rev___Office_Currency_Prev_Yr,">0")
But when I next them together, I get an error. I can't get my head around it.
=COUNTIFS(Registration_Type_Prev_Yr,"Delegate",Sales_Method_Prev_Yr,"",Order_Status_Prev_Yr,"<>Cancelled",Del_Rev___Office_Currency_Prev_Yr,">0",Job_Function_PY,"<>*FE501*")

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)

Thanks

 

Will

5 Replies

@WillWWBR 

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  

@WillWWBR 

It works!

S0972.png

But you also have invalid named ranges:

S0973.png

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!!!