Excel formula problem

[Spicealdous]

I apologise if this a very simple request!

Let me set the scene:
I have a range of 20 cells each containing various figures, these figures are entered into cells A1:A20.

I want to do a countif formula that will count all the cells where the value is between 10 and 20 including 10 and 20.

So far I have got =countif(A1:A20,">=10&quot

but i dont know the rest.

Any help would be gratefully received.

 

[TonyJollans]

Hi Spicealdous,

You can't do it with COUNTIF; what you want is SUMPRODUCT ..

=SUMPRODUCT((A1:A20>=10)*(A1:A20<=20))

.. should give you what you are looking for.

Enjoy,
Tony

 

[Loomah]

Hi
I knew there had to be an easier way than the one I came up with but it wouldn't come to me! Just for the record here is the longer, more cumbersome and, frankly, pointless use of countif that I ended up with!!

=(COUNTIF(NamedRange,&quot;>=10&quot+COUNTIF(NamedRange,&quot;<20&quot)-COUNT(NamedRange)

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[GlennUK]

Loomah,

a slightly less pointless, but still cumbersome formula is:

=COUNTIF(NamedRange,&quot;>=10&quot-COUNTIF(NamedRange,&quot;>20&quot

Glenn.

 

[Loomah]

Glenn
You're right - good call!!
I dunno what was wrong with me yesterday (probably lack of practice!) but I knew I wanted to use SUMPRODUCT but for some reason it wouldn't come to me. What makes that worse is how simple the solution really is.

BTW there's an error in my formula
should read &quot;<=20&quot; not &quot;<20&quot;
Duh
;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244