counting cells with a criteria

N

Nick Horn

Hi

Please can anyone help.

I need to count a group of cells that are spread through the spreadsheet
which meet a criteria range e.g

count the number of cells for X2, BX2, CX2 that are greater than or equal to
40 but less than or equal to 49.

I cant seem to use COUNTIF. Presumably because it needs a range e.g. X2:CX2
and I do not not want to count all cells in the range, just specific cells.

Many thaks for any help.

Nick
 
B

Bernard Liengme

Hello Nick:
If its just three cells try
=AND(X2>=40,X2<=49)+AND(BX2>=40,BX2<=49)+AND(CX2>=40,CX2<=49)
best wishes
 
F

Fred Smith

If you have only three cells, you can just use a formula. It also helps to know
that Excel treats TRUE as 1, and FALSE as 0. So your formula would be:

=and(X2>=40,x2<=49)+and(bx2>=40,bx2<=49)+and(cx2>=40,cx2<=49)
 
N

Nick Horn

Hi Bernard

Thanks for this. The total number of cells is 11. Will this cause a problem?
 
N

Nick Horn

Hi fred

Thanks to both of you fro solving this.

Fred Smith said:
If you have only three cells, you can just use a formula. It also helps to know
that Excel treats TRUE as 1, and FALSE as 0. So your formula would be:

=and(X2>=40,x2<=49)+and(bx2>=40,bx2<=49)+and(cx2>=40,cx2<=49)
Click to expand...
 
B

Bob Phillips

Maybe shorter

=SUMPRODUCT(COUNTIF(INDIRECT({"X2","BX2","CX2"}),">=40"))
-SUMPRODUCT(COUNTIF(INDIRECT({"X2","BX2","CX2"}),">=50"))

extend as required

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)
 
Top