CountIFS Assistance Please

[Taff]

I have this formula which returns a correct result:
=COUNTIFS(E0!BP2:BP2910,">2.5",E0!AZ2:AZ2910,">2.2",E0!AZ2:AZ2910,"<2.7")

When I substitute the exact criteria with a cell reference, with the same criteria in, I get a 0
=COUNTIFS(E0!BP2:BP2910,">A4",E0!AZ2:AZ2910,">B4",E0!AZ2:AZ2910,"<C4")

I am trying to build a query where I can quickly point/change to the criteria without having to go in and change each formula.

Any ideas how to achieve would be appreciated.

Thanks

Taff

 

[jtakw]

Hi,

Your values in A4, B4, and the missing third, are they manually entered or from a formula?
Your original formula using 2.5 is a number, but is different if A4 is "2.5" (which is Text).

 

[Taff]

they are manually entered.

the countifs formula appears to enter them as text automatically. I have tried removing the "" but it won't let me.

well spotted the formula not working is:
=COUNTIFS(E0!BP2:BP2910,">A4",E0!AZ2:AZ2910,">B4",E0!AZ2:AZ2910,"<C4")

 

[jtakw]

they are manually entered.

the countifs formula appears to enter them as text automatically. I have tried removing the "" but it won't let me.

What do you mean it won't let you?

 

[Aladin Akyurek]

=COUNTIFS(E0!BP2:BP2910,">"&A4,E0!AZ2:AZ2910,">"&B4,E0!AZ2:AZ2910,"<"&C4)

where A4 = 2.5, B4 = 2.2, and C4 = 2.7, would produce the same result as:

=COUNTIFS(E0!BP2:BP2910,">2.5",E0!AZ2:AZ2910,">2.2",E0!AZ2:AZ2910,"<2.7")​

​

 

[jtakw]

Aladin's correct, you have your "" quotes misplaced in your formula.

 

[Taff]

Aladin's correct, you have your "" quotes misplaced in your formula.

thank you

you are gentlemen & scholars, worked lovely

cheers

Taff

 

[jtakw]

You're welcome, glad you have it working.