Noncontiguous Cells or Ranges in a Formula?
 

Hello,

Does anyone know how to select noncontiguous ranges or cells for the
inclusion into formulas?. Take a look at a formula I pasted below.
A friend at work entered this and it works for what I want to do,
but, I need to do this for up to 124 cells in a sheet, that are
noncontiguous. Looking at my formula you will see where I'm having to
"countif" each cell separately. We believe there is a better way, but
can't seem to find an example anywhere. All suggestions appreciated!

Mark

=COUNTIF((Chemistry!$D$3),D5)+COUNTIF((Chemistry!$ D$10),D5)
+COUNTIF((Chemistry!$D$17),D5)+COUNTIF((Chemistry! $D$24),D5)
+COUNTIF((Chemistry!$D$31),D5)+COUNTIF((Chemistry! $D$44),D5)

Noncontiguous Cells or Ranges in a Formula?
 

Take aook at pivot tables.
Or try DataFilter
you can choose regular filter or advanced

Or in code you can loop through column D and add 1 each time a cell = D5
======================
x = 0
For rw = 6 to 44
If cells(rw,4) = cells(5,4) then
x = x + 1
end if

msgbox x
=======================
If you don't want to look at all cells in column D
you can use a Select Case structure...
--
steveB

Remove "AYN" from email to respond
"meverly9<REMOVETHIS @comcast.net" <"meverly9<REMOVETHIS wrote in message
...
Hello,

Does anyone know how to select noncontiguous ranges or cells for the
inclusion into formulas?. Take a look at a formula I pasted below.
A friend at work entered this and it works for what I want to do,
but, I need to do this for up to 124 cells in a sheet, that are
noncontiguous. Looking at my formula you will see where I'm having to
"countif" each cell separately. We believe there is a better way, but
can't seem to find an example anywhere. All suggestions appreciated!

Mark

=COUNTIF((Chemistry!$D$3),D5)+COUNTIF((Chemistry!$ D$10),D5)
+COUNTIF((Chemistry!$D$17),D5)+COUNTIF((Chemistry! $D$24),D5)
+COUNTIF((Chemistry!$D$31),D5)+COUNTIF((Chemistry! $D$44),D5)