| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif in non adjacent columns
Is it possible to use countif to count the number of times a certain number
appears in column A, C, E...etc through to W, but ignoring columns B, D, F, etc through to X I can only get the countif to work if I use the full range (A1:X1) |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Countif in non adjacent columns
Just add them up:
=COUNTIF(A:A,13)+COUNTIF(C:C,13) If you have many columns, it is better to use SUMPRODUCT() -- Gary''s Student - gsnu200806 "mickjjuk" wrote: Is it possible to use countif to count the number of times a certain number appears in column A, C, E...etc through to W, but ignoring columns B, D, F, etc through to X I can only get the countif to work if I use the full range (A1:X1) |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Countif in non adjacent columns
Try the following formula, which needs to be confirmed with
CONTROL+SHIFT+ENTER... =SUM(IF(MOD(COLUMN(A1:X1)-COLUMN(A1),2)=0,IF(A1:X1=A5,1))) ....where A5 contains the number of interest. Hope this helps! In article , mickjjuk wrote: Is it possible to use countif to count the number of times a certain number appears in column A, C, E...etc through to W, but ignoring columns B, D, F, etc through to X I can only get the countif to work if I use the full range (A1:X1) |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Countif in non adjacent columns
Thanks for the help, it works fine. This seems like the 'tidier' option of
the 2 suggestions. "Domenic" wrote: Try the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER... =SUM(IF(MOD(COLUMN(A1:X1)-COLUMN(A1),2)=0,IF(A1:X1=A5,1))) ....where A5 contains the number of interest. Hope this helps! In article , mickjjuk wrote: Is it possible to use countif to count the number of times a certain number appears in column A, C, E...etc through to W, but ignoring columns B, D, F, etc through to X I can only get the countif to work if I use the full range (A1:X1) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Add adjacent cell value to existing CountIF formula | Excel Worksheet Functions | |||
| Using CountIf with non-adjacent cells in same column | Excel Discussion (Misc queries) | |||
| Countif across multiple non-adjacent cells | Excel Discussion (Misc queries) | |||
| countif = < AND value in adjacent columns match criteria | Excel Worksheet Functions | |||
| How do I use countif across non-adjacent cells? | Excel Worksheet Functions | |||
Hybrid Mode
