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)

Take aook at pivot tables.
Or try Data>Filter
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
news:[email protected]...
> 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)