Error on Countif

[klynshoe]

Hello,

I have a countif formula where I've tested each individual criteria and they all work. However, when I put them all together they don't (I get a #VALUE! error) What am I doing wrong??

=COUNTIFS(Floor,2,Final_Product,$A4,Occupant_Proper,"<>Open")

These work just fine!!!

=COUNTIFS(Floor,2)
=COUNTIFS(Final_Product,$A4)
=COUNTIFS(Occupant_Proper,"<>Open")





USING EXCEL 2007

 

[njimack]

Are all 3 ranges the same size?

 

[T. Valko]

Hello,

I have a countif formula where I've tested each individual criteria and they all work. However, when I put them all together they don't (I get a #VALUE! error) What am I doing wrong??

=COUNTIFS(Floor,2,Final_Product,$A4,Occupant_Proper,"<>Open")

These work just fine!!!

=COUNTIFS(Floor,2)
=COUNTIFS(Final_Product,$A4)
=COUNTIFS(Occupant_Proper,"<>Open")





USING EXCEL 2007

Are all of the named ranges the same size?

 

[klynshoe]

Yes they are all the same size:

Occupant_Proper =Floor_Data!$F$4:$F$8462
Final_Product =Floor_Data!$O$5:$O$8462
Floor = =Floor_Data!$B$4:$B$8462

 

[T. Valko]

Yes they are all the same size:

Occupant_Proper =Floor_Data!$F$4:$F$8462
Final_Product =Floor_Data!$O$5:$O$8462
Floor = =Floor_Data!$B$4:$B$8462

Hmmm...

Does this array formula** give the correct result:

=SUM(IF(Floor=2,IF(Final_Product=$A4,IF(Occupant_Proper<>"Open",1))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[njimack]

Yes they are all the same size:

Occupant_Proper =Floor_Data!$F$4:$F$8462
Final_Product =Floor_Data!$O$5:$O$8462
Floor = =Floor_Data!$B$4:$B$8462

Is that a typo or is Final_product 1 row less than the other ranges?

 

[klynshoe]

njijack -

You are right it was one row less - I must be blind. I fixed it and woolaa it worked!!!!!

THANKS!!!!!!!!!!!!!!!

 

[T. Valko]

njijack -

You are right it was one row less - I must be blind.QUOTE]
I already knew I was!