Does the COUNTIF range have to be a continuous range (i.e. A1:E1) or
could it be a non-continuous range of cells (i.e. A1,D1,G1,J1,M1)? I
want to count the number of occurances of a cell value being > 75 in
the non-continuous range of A1,D1,G1,J1,M1. The COUNTIF function does
not like the non-continuous range. Is there a better way of doing
this. It is not an option to move the columns of data so they are a
continuous range.

Mike


--
mluetkem
------------------------------------------------------------------------
mluetkem's Profile: http://www.excelforum.com/member.php...fo&userid=2316
View this thread: http://www.excelforum.com/showthread...hreadid=396616

Mike,

I think COUNTIF must be contiguous. But you can get around it

=SUMPRODUCT(--(MOD(COLUMN(A1:M1),3)=1),A1:M1)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"mluetkem" <(E-Mail Removed)> wrote in
message news:(E-Mail Removed)...
>
> Does the COUNTIF range have to be a continuous range (i.e. A1:E1) or
> could it be a non-continuous range of cells (i.e. A1,D1,G1,J1,M1)? I
> want to count the number of occurances of a cell value being > 75 in
> the non-continuous range of A1,D1,G1,J1,M1. The COUNTIF function does
> not like the non-continuous range. Is there a better way of doing
> this. It is not an option to move the columns of data so they are a
> continuous range.
>
> Mike
>
>
> --
> mluetkem
> ------------------------------------------------------------------------
> mluetkem's Profile:
http://www.excelforum.com/member.php...fo&userid=2316
> View this thread: http://www.excelforum.com/showthread...hreadid=396616
>

One way:

=SUMPRODUCT(--(MOD(COLUMN(A1:M1),3)=1),--(A1:M1>75))

In article <(E-Mail Removed)>,
mluetkem <(E-Mail Removed)>
wrote:

> Does the COUNTIF range have to be a continuous range (i.e. A1:E1) or
> could it be a non-continuous range of cells (i.e. A1,D1,G1,J1,M1)? I
> want to count the number of occurances of a cell value being > 75 in
> the non-continuous range of A1,D1,G1,J1,M1. The COUNTIF function does
> not like the non-continuous range. Is there a better way of doing
> this. It is not an option to move the columns of data so they are a
> continuous range.
>
> Mike