counting cells with a criteria
 

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

counting cells with a criteria
 

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
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Nick Horn" <Nick wrote in message
...
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

counting cells with a criteria
 

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)

--
Regards,
Fred


"Nick Horn" <Nick wrote in message
...
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

counting cells with a criteria
 

Hi Bernard

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

"Bernard Liengme" wrote:

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
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Nick Horn" <Nick wrote in message
...
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

counting cells with a criteria
 

Only to your typing fingers!
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Nick Horn" wrote in message
...
Hi Bernard

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

"Bernard Liengme" wrote:

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
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Nick Horn" <Nick wrote in message
...
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

counting cells with a criteria
 

Many thanks. Eyes are now slightly crossed. :)

"Bernard Liengme" wrote:

Only to your typing fingers!
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Nick Horn" wrote in message
...
Hi Bernard

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

"Bernard Liengme" wrote:

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
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Nick Horn" <Nick wrote in message
...
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

counting cells with a criteria
 

Hi fred

Thanks to both of you fro solving this.

"Fred Smith" wrote:

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)

--
Regards,
Fred


"Nick Horn" <Nick wrote in message
...
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

counting cells with a criteria
 

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)

"Nick Horn" wrote in message
...
Hi fred

Thanks to both of you fro solving this.

"Fred Smith" wrote:

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)

--
Regards,
Fred


"Nick Horn" <Nick wrote in message
...
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