Getting #Value! error when using Countifs, very confusing...

[TomMcC]

I'm getting an error (#Value!) when using Countifs with multiple criteria. I've tried creating an example file to reproduce the error, but so far all my example files have worked perfectly, so I know I have something peculiar in my data. My problem is I don't know how to check the data to find the error....

To the best of my ability to describe, here's my situation:

I have a large multi-column data table in one sheet of my file, with each column being represented by a named range. Column 1 is 'Issue Type', Column 2 is 'Region', Column 3 is 'Month'. My other sheet includes a summary table, reporting on the number of times within a certain month that each 'Issue Type' occurs within a specific 'Region'. The first column of my summary table is a listing of unique entries for column 1 in the data, obtained with the Unique function (Excel in Office365). The second column is intended to be the # of times that each specific 'Issue Type' occurs within the relevant month and 'Region'. I have named ranges defined for both the month and region being tested for.

Here's my formula:

=COUNTIFS(Region,Region1,MonthYrOpened,ReportingPeriod,Issue,(Q14)) *Q14 refers back to the cell with the first result from the Unique(List) function.

That formula returns the #Value! error. The completely confusing part is that if I modify the formula to test ONLY if the 'Issue Type' from the data table matches the result in Q14, I get a numeric result. If I test for all occurrences that match both the 'Region' and 'Month', I get a numeric result. However, if I combine the tests, as represented by the formula above, I get the #Value! error.

My apologies for the long-winded description, and especially for the lack of a (non-working) example file. At this point, I'll gladly provide any additional information that might help resolve the problem.

Thanks in advance, I appreciate your time and patience.

Tom

[Fluff13]

Are all you ranges the same size?

[TomMcC]

And just like that, you solved it. This had me stumped for hours, bud !

THanks, can't begin to say how much I appreciate the help.

Tom

[Fluff13]

You're welcome & thanks for the feedback.

[Mr_Phil]

Are all you ranges the same size?

Here I come with the same problem and it's been fixed by you. Your Mojo is definitly not upside down. Thanks.