How do I... (COUNTIF Function question)

[roadstain]

How do I use the COUNTIF Function when another cell range is equal or greater to a number value.

I have an extract from an advanced filter copied to another worksheet. I need to count the instances of a <value> in column A (I have a name cell range for this) when equal to a second name cell range. That I can use the COUNTIF function for without issue. The problem is I only want to count those instances when a third named cell range is (column K - also a named cell range) is greater than 0.

What would be the best way to do this?

 

[euskadi]

so your values are in range arange and you want to compare to range brange .... deriving the count of cells in &quot;a&quot; which are greater than the corresponding cell in &quot;b&quot;... plus your third range krange has to be greater than 0.

you can do this with an array formula:

=SUM(IF(arange=brange,IF(krange>0,1,0),0))

enter this in your cell where you want to see the result, and hit CRTL+SHIFT+ENTER rather than just ENTER when you enter the formula.

Let me know if that works out for you.

 

[roadstain]

It seemed to work but the number count seems to be off on some of the extracts. Looking into this now. Thanks for the help on this.

 

[roadstain]

Ok, After looking at this again the array function is returning true or &quot;1&quot; no matter if the krange is greater than zero or not. So all the instances in krange are being counted regardless of the value. Will the above array formula return characters as being greater than zero?

 

[euskadi]

That's odd. I get similarly odd results, it's counting anything if I enter numbers >0 or text. Maybe tweak the formula and try this:

=SUM(IF(arange=brange,IF(krange<=0,0,1),0))

 

[roadstain]

I completed this another way - thanks. I used the above but changed the krange IF statement to be a <> or not equals to.