CountIF

[Bob Phillips]

=SUMPRODUCT(--(D223>9),--(D223<20))


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[cteacher]

I am trying to count a range of cells with multiple conditions. I know
CountIf will not allow two conditions; however, I am trying to count cells
with numbers in cells D2:d23 >9 and <20.

Please help!

 

[Guest]

Try this...

=countif(d2:d23,and(>9,<20))

Using AND should check that both conditions are true before returning true
to the countif.

 

[Guest]

=COUNTIF(D223<"9")-COUNTIF(D223<"20")

Subtract the count of numbers less than 9 from the count of numbers less
than 20, and you will have the count of all numbers from 9 to 19, inclusive.

Dave

 

[cteacher]

Hi Rob,

This only gives me a zero. Looking at my range of numbers, it should be 1.

 

[cteacher]

Hi Dave,

When I tried this formula it gives me a -1 and not 1.

 

[cteacher]

Here are the range of numbersL

69
13
100
81
63
81
75
25
69
69
81
75
63
81
81
44
69
81
94
69
75
81

 

[Guest]

It gave you -1 because I reversed it.

It should be:

=COUNTIF(D223<"20")-COUNTIF(D223<"9")

Sorry about that.

Dave

 

[cteacher]

Hi Bob.

Thank you!!!!! This it it!!!!!!!!!

=SUMPRODUCT(--(D223>9),--(D223<20))

 

[David Biddulph]

Dave,

I guess you intended to say
=COUNTIF(D223,"<20")-COUNTIF(D223,"<9")

Note the commas between the arguments for the COUNTIF function, and also the
positioning of the double quotes.

 

[Sandy Mann]

=COUNTIF(D223<"20")-COUNTIF(D223<"9")

Dave,

Does that construction work for you?

I need to change it to:

=COUNTIF(D223,"<20")-COUNTIF(D223,"<9")

in XL97 Professional to get it to work.

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Guest]

See David Biddulph's correction. His syntax is correct.