Share via

Why do I get a #VALUE! error when using COUNTIFS to check if a record appears in another worksheet of the same workbook?

Anonymous
2023-01-17T23:36:48+00:00

The formula I am using is:

=COUNTIFS(NoResponse!B:B,B2,NoResponse!C:C,C2)

I am trying to count the records in worksheet NoResponse for which column B = cell B2 and column c = C2 (B2 and C2 being cells in the worksheet this formula is in).

When I look at the evaluation of the arguments using the fx button it gives me the #VALUE! error beside the ranges (i.e. NoResponse!B:B and NoResponse!C:C)

However the formula evaluates correctly in all but a few cases. I discovered the #VALUE! error when investigating those cases.

NB: there are no linked workbooks

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Ashish Mathur 102K Reputation points Volunteer Moderator
    2023-01-18T03:28:41+00:00

    Share the download link of the workbook.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2023-01-18T03:21:47+00:00

    Thanks but there aren't any errors in those columns. They contain only text or blank cells.

    Was this answer helpful?

    0 comments
  3. Ashish Mathur 102K Reputation points Volunteer Moderator
    2023-01-18T02:53:15+00:00

    Hi,

    There are errors in columns B and/or C. Delete those errors.

    Was this answer helpful?

    0 comments