| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Linking Groups of cells between workbooks | Excel Discussion (Misc queries) | |||
| Counting cells using multiple criteria | Excel Worksheet Functions | |||
| Counting blank cells until value is reached | Excel Worksheet Functions | |||
| checking that cells have a value before the workbook will close | Excel Worksheet Functions | |||
| Counting the number of cells meeting conditional formating criteria | Excel Worksheet Functions | |||
Linear Mode
