COUNTIFs formula returning a #VALUE!

Hi all,

First time of the forum, so I imagine this has been answered already...
I'm trying to get a number count from multiple criterias and I'm using the following COUNTIFS formula

=COUNTIFS('[Encounter_Design.xlsx]Low 1'!$G:$G, "NCL", '[Encounter_Design.xlsx]Low 1'!$AP:$AZ, "yes")

I am pulling the information from another excel doc.

However when I run this formula I get a #VALUE! outcome. I don't think it's anything to do with the fact I'm pulling the info from another doc as when I run a similar formula using COUNTIF it works fine.

Basically I only want to get a count if G:G is NCL and AP:AZ is yes

Thanks

Countifs needs the ranges to be of the same dimensions
Both would need to be single column criteria ranges.

So you'd have to do countifs(col1)+countifs(col2)+countifs(col3)

I know, that's tedious. And yes there is a better way

Try
=SUMPRODUCT(('[Encounter_Design.xlsx]Low 1'!$G:$G="NCL")*('[Encounter_Design.xlsx]Low 1'!$AP:$AZ="yes"))

However, I strongly recommend using definate ranges instead of entire columns.
Even though XL2007+ allows it, it is still a major performance hit with sumproduct.

So keep the row#s limited to around 10% more than is actually needed
So if you actually have 1000 rows, just use 1100 rows in the ranges

=SUMPRODUCT(('[Encounter_Design.xlsx]Low 1'!$G1:$G1100="NCL")*('[Encounter_Design.xlsx]Low 1'!$AP1:$AZ1100="yes"))

Yea, this worked...And you were right, excel wigged out using an entire columns as a range!

All sorted now! Thanks

Glad to help, thanks for the feedback.