Countif and non adjacent cells range

**isouaga** · 05-17-2007, 08:27 AM

I have some numbers in cells A1:A10. I want to count the number of numbers that are greater than 7 in non adjacent cells (for example in A2, A4 and A6).
I tried with the COUNTIF function but the function won't accept a range such as A2,A4,A6. It returns a #Value error for the range.
How can I do counting with a condition in non adjacent cells?
Thank you for your help.

**sweep** · 05-17-2007, 08:43 AM

Hi,

This isn't exactly what you want, but you may be able to adapt it to your needs.

=COUNT(IF(ROW(A1:A10)=EVEN(ROW(A1:A10)),IF(A1:A10>7,A1:A10,""),""))

This counts all the even rows in the range, if they're greater than 7

It's an array formula, so enter with CTRL, SHIFT AND ENTER

**isouaga** · 05-17-2007, 09:02 AM

Thank you Dave. In fact I was just using A2, A4 and A6 as an example. In reality, the cells I want to count from are not even, they really don't follow any pattern.

**daddylonglegs** · 05-17-2007, 09:56 AM

How many cells are involved? If it's a low number as per your example try

=(A2>7)+(A4>7)+(A6>7)

Otherwise, are they really random or are the cells you want to look at defined by some text in another column or something else?

**sweep** · 05-17-2007, 10:48 AM

I think this does it - can someone please check I'm not barking up the wrong tree here?

=INDEX(FREQUENCY((range),max_value),2)

eg.,

=INDEX(FREQUENCY((A1,A4,A8,A12,C7,C6,C5),7),2)

counts all the values in the specified cells greater than 7.

Dave

**daddylonglegs** · 05-17-2007, 10:55 AM

Looks good to me, Dave

**NBVC** · 05-17-2007, 11:05 AM

Originally Posted by sweep

I think this does it - can someone please check I'm not barking up the wrong tree here?

=INDEX(FREQUENCY((range),max_value),2)

eg.,

=INDEX(FREQUENCY((A1,A4,A8,A12,C7,C6,C5),7),2)

counts all the values in the specified cells greater than 7.

Dave

I like that too