counting cells with a criteria

[Guest]

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

 

[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

 

[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)

 

[Guest]

Hi Bernard

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

 

[Bernard Liengme]

Only to your typing fingers!

 

[Guest]

Many thanks. Eyes are now slightly crossed.

Only to your typing fingers!

 

[Guest]

Hi fred

Thanks to both of you fro solving this.

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)

 

[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)