Countifs referencing data from another worksheet in the same workbook

Hi

I'm trying to use the countifs function where it's referencing data on another worksheet but within the same workbook.

The worksheet which has my Countifs formula is "Analysis"
The worksheet which has the data I'm referencing is "Leave"

There are 2 sets of criteria I've used in the formula.

=COUNTIFS(Leave!$C:$H,Analysis!$B2,Leave!$B:$B,Analysis!C$1)

I however get a #VALUE! error. Having google'd this error, it advises that it results from the data referenced being from a closed workbook, however I'm referencing from the same (open) workbook but from a different worksheet.

Any help would be appreciated.

Thanks

Hi, welcome to the forum

COUNTIFS needs a single column reference, it wont work with an array like that.

Can you upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

It sounds like you are trying to use the paperclip icon to upload. That hasn't worked for some time.

To attach a file to your post,

• click “Go Advanced” (next to Post Quick Reply – bottom right),
• scroll down until you see “Manage Attachments”, click that,
• click “Browse”.
• select your file(s)
• click “Upload”
• click “Close window”
• click “Submit Reply”
• be sure to desensitize the data

The file name will appear at the bottom of your reply.

I believe that the following formula will do what you want:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools link above your first post. I hope that you have a blessed day.