=SUMPRODUCT(--(D2:D239),--(D2:D23<20))


--
HTH

Bob Phillips

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

"cteacher" <u27106@uwe wrote in message news:66d3c360a0feb@uwe...
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!

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!

Try this...

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

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

"cteacher" wrote:

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!

=COUNTIF(D2:D23<"9")-COUNTIF(D2:D23<"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
--
Brevity is the soul of wit.


"cteacher" wrote:

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!

Hi Rob,

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

Rob J wrote:
Try this...

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

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

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!

Hi Dave,

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

Dave F wrote:
=COUNTIF(D2:D23<"9")-COUNTIF(D2:D23<"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
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!

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


Rob J wrote:
Try this...

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

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

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!

It gave you -1 because I reversed it.

It should be:

=COUNTIF(D2:D23<"20")-COUNTIF(D2:D23<"9")

Sorry about that.

Dave
--
Brevity is the soul of wit.


"cteacher" wrote:

Hi Dave,

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

Dave F wrote:
=COUNTIF(D2:D23<"9")-COUNTIF(D2:D23<"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
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!

Hi Bob.

Thank you!!!!! This it it!!!!!!!!!
Bob Phillips wrote:
=SUMPRODUCT(--(D2:D239),--(D2:D23<20))

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!

Dave,

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

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

"Dave F" wrote in message
...
It gave you -1 because I reversed it.

It should be:

=COUNTIF(D2:D23<"20")-COUNTIF(D2:D23<"9")

Sorry about that.

"cteacher" wrote:

Hi Dave,

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

Dave F wrote:
=COUNTIF(D2:D23<"9")-COUNTIF(D2:D23<"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
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!

"Dave F" wrote in message
...

=COUNTIF(D2:D23<"20")-COUNTIF(D2:D23<"9")

Dave,

Does that construction work for you?

I need to change it to:

=COUNTIF(D2:D23,"<20")-COUNTIF(D2:D23,"<9")

in XL97 Professional to get it to work.

--
Regards,

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


with @tiscali.co.uk

See David Biddulph's correction. His syntax is correct.
--
Brevity is the soul of wit.


"Sandy Mann" wrote:

"Dave F" wrote in message
...

=COUNTIF(D2:D23<"20")-COUNTIF(D2:D23<"9")

Dave,

Does that construction work for you?

I need to change it to:

=COUNTIF(D2:D23,"<20")-COUNTIF(D2:D23,"<9")

in XL97 Professional to get it to work.

--
Regards,

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


with @tiscali.co.uk