| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do i create a countif formula in excel?
I need to count all the nonconsecutive cells in a given range that contain a
specific # 1-9. I have a formula that counts all the cells in the given range: =countif(c4:k12,"1"). I need to modify it to count only the cells d5,d8,d11,g5,g8,g11,j5,j8,j11 that contain the #1. I know there must be a way but i am not familure enough with creating formulas to figure it out. Please help. I am using excel 2002. jp |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do i create a countif formula in excel?
=SUMPRODUCT(COUNTIF(INDIRECT({"D5","D8","D11","G5" ,"G8","G11","J5","J8","J11
"}),1)) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "jp" wrote in message ... I need to count all the nonconsecutive cells in a given range that contain a specific # 1-9. I have a formula that counts all the cells in the given range: =countif(c4:k12,"1"). I need to modify it to count only the cells d5,d8,d11,g5,g8,g11,j5,j8,j11 that contain the #1. I know there must be a way but i am not familure enough with creating formulas to figure it out. Please help. I am using excel 2002. jp |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do i create a countif formula in excel?
If you have the free morefunc.xll add-in installed:
=SUMPRODUCT((ARRAY.JOIN(D5,D8,D11,G5,G8,G11,J5,J8, J11)=1)+0) Otherwise: =SUM(IF(MOD(ROW(D5:J11)-ROW(D5)+0,3)=0,IF(MOD(COLUMN(D5:J11)-COLUMN(D5)+0,3)=0,(D5:J11=1)+0))) which must be confirmed with control+shift+enter, not just with enter. jp wrote: I need to count all the nonconsecutive cells in a given range that contain a specific # 1-9. I have a formula that counts all the cells in the given range: =countif(c4:k12,"1"). I need to modify it to count only the cells d5,d8,d11,g5,g8,g11,j5,j8,j11 that contain the #1. I know there must be a way but i am not familure enough with creating formulas to figure it out. Please help. I am using excel 2002. jp |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| TRYING TO SET UP EXCEL SPREADSHEET ON MY COMPUTER | New Users to Excel | |||
| Can I create an IF formula for single cell w/ dif. text in Excel? | Excel Discussion (Misc queries) | |||
| Can I create a formula in Excel that evaluates form information? | Excel Worksheet Functions | |||
| Create an if-then formula in Excel to limit column total? | Excel Discussion (Misc queries) | |||
| How do I create a formula in Excel that will countif or sumif bef. | Excel Worksheet Functions | |||
Hybrid Mode
