countifs help

[Flashboy]

trying to do a countifs that keeps returning 0 when I know there should be a count of 11.

This is the formula I'm using, I need to be able to put the individual criteria in such as BVH111 just not sure if I'm doing it right? any help would be great.

=COUNTIFS(Data!$CA:$CA,'NS Dashboard'!D5,Data!$H:$H,BVH151+BVH152+BVH153+BVH154+BVH155+BVH156+BVH157+BVH161+BVH162+BVH162+BVH163+BVH164+BVH165+BVH166+BVH167)

 

[Jonmo1]

The way that formula is writtin, it will first ADD the values in each of those cells (BVH151 through BVH167)
Say those add up to 35 or whatever.
Then 35 becomes the criteria it searches column H for
=COUNTIFS(Data!$CA:$CA,'NS Dashboard'!D5,Data!$H:$H,BVH151+BVH152+BVH153+BVH154+BVH155+BVH156+BVH157+BVH161+BVH162+BVH162+BVH163+BVH164+BVH165+BVH1 66+BVH167)
=COUNTIFS(Data!$CA:$CA,'NS Dashboard'!D5,Data!$H:$H,35)

Is that what you're intending it to do?

Or are you trying to have an OR criteria for column H
Count if Column H = BVH151 OR BVH152 OR BVH153 etc.. ??

For that you would do
=SUMPORODUCT(COUNTIFS(Data!$CA:$CA,'NS Dashboard'!D5,Data!$H:$H,$BVH$151:$BVH$167))

 

[tybaltlives]

I can see a typo error in the equation. Towards the right. BVH1 66 may need to be BVH166.

 

[Flashboy]

What I was trying to do is count if it find all of the BVH numbers stated above. as there are 100's more but I just want them to be included if that makes sense?

 

[Jonmo1]

Did you try the formula I posted?

 

[Flashboy]

Yea didn't work at first but then spotted why, had an extra O in sumproduct . Thanks Your a star!

 

[Jonmo1]

It sure did dint it
Good catch.

Glad to help