COUNT BETWEEN A RANGE AND

Dave · May 21, 2009

HI

I WOULD LIKE TO COUNT VALUES BETWEEN A RANGE. THE FORMULA I HAVE TRIED TO
USE IS: COUNTIF(D

,">30000AND<75000") TO GET THE NUMBER OF VALUES BETWEEN 30
AND 75, HOWEVER, THIS DOES NOT WORK. CAN SOMEONE HELP?

ALSO, ON TOP OF THIS I WOULD LIKE TO COUNT A STATEMENT IN ANOTHER COLUMN
WHICH SAYS YES OR NO. SO THAT FROM THE VALUES BETWEEN 30 AND 75 I WOULD LIKE
TO KNOW WHICH ONES ARE ALSO YES AND NO. ANYONE WHO CAN HELP ME MUCH
APPRECIATED

Jacob Skaria · May 21, 2009

Either use
=COUNTIF(D

,">30000")-COUNTIF(D

,">75000")

OR
=SUMPRODUCT(--(D1

1000>30000),--(D1

1000<75000))

With another condition
=SUMPRODUCT(--(C1:C1000="Y"),--(D1

1000>30000),--(D1

1000<75000))

=SUMPRODUCT(--(C1:C1000="N"),--(D1

1000>30000),--(D1

1000<75000))

Jacob Skaria · May 21, 2009

Either use
=COUNTIF(D

,">30000")-COUNTIF(D

,">75000")

OR
=SUMPRODUCT(--(D1

1000>30000),--(D1

1000<75000))

With another condition
=SUMPRODUCT(--(C1:C1000="Y"),--(D1

1000>30000),--(D1

1000<75000))

=SUMPRODUCT(--(C1:C1000="N"),--(D1

1000>30000),--(D1

1000<75000))

Dave · May 21, 2009

Hi Jacob

Thanks for this.

The SUMPRODUCT formula doesn seem to be working as it stands. What do the 2
hyphens represent~?

Dave · May 21, 2009

Hi Jacob

Thanks for this.

The SUMPRODUCT formula doesn seem to be working as it stands. What do the 2
hyphens represent~?

Jacob Skaria · May 21, 2009

You must be using 2003. SUMPRODUCT should work with a defined range...Try
with D1

1000

=SUMPRODUCT(--(D1

100>30000),--(D1

100<75000))

Refer this link for --
http://www.mcgimpsey.com/excel/formulae/doubleneg.html

If this post helps click Yes

Jacob Skaria · May 21, 2009

You must be using 2003. SUMPRODUCT should work with a defined range...Try
with D1

1000

=SUMPRODUCT(--(D1

100>30000),--(D1

100<75000))

Refer this link for --
http://www.mcgimpsey.com/excel/formulae/doubleneg.html

If this post helps click Yes

Dave · May 21, 2009

Hi

The formula works now for values between 30k and 75k as I have put these
values to record the correct amount of rows, however, the formula:
=SUMPRODUCT(--(C1:C1000="N"),--(D1

1000>30000),--(D1

1000<75000)) does not
work, I have written it as:
=SUMPRODUCT(--(F1:F5000="Yes"),--(D1

5000>30000),--(D1

5000<75000)) and
this records a value of 256 (which is wrong) it sould be 324. If I change it
to "No" it records a value of 1,014 (again incorrect) it should 948.

Dave · May 21, 2009

Hi

The formula works now for values between 30k and 75k as I have put these
values to record the correct amount of rows, however, the formula:
=SUMPRODUCT(--(C1:C1000="N"),--(D1

1000>30000),--(D1

1000<75000)) does not
work, I have written it as:
=SUMPRODUCT(--(F1:F5000="Yes"),--(D1

5000>30000),--(D1

5000<75000)) and
this records a value of 256 (which is wrong) it sould be 324. If I change it
to "No" it records a value of 1,014 (again incorrect) it should 948.

Dave · May 21, 2009

MY APOLOGIES!!! IT DOES WORK!!

THANKS!!

Dave said:
Hi

The formula works now for values between 30k and 75k as I have put these
values to record the correct amount of rows, however, the formula:
=SUMPRODUCT(--(C1:C1000="N"),--(D11000>30000),--(D11000<75000)) does not
work, I have written it as:
=SUMPRODUCT(--(F1:F5000="Yes"),--(D15000>30000),--(D15000<75000)) and
this records a value of 256 (which is wrong) it sould be 324. If I change it
to "No" it records a value of 1,014 (again incorrect) it should 948.

Dave · May 21, 2009

MY APOLOGIES!!! IT DOES WORK!!

THANKS!!

Dave said:
Hi

The formula works now for values between 30k and 75k as I have put these
values to record the correct amount of rows, however, the formula:
=SUMPRODUCT(--(C1:C1000="N"),--(D11000>30000),--(D11000<75000)) does not
work, I have written it as:
=SUMPRODUCT(--(F1:F5000="Yes"),--(D15000>30000),--(D15000<75000)) and
this records a value of 256 (which is wrong) it sould be 324. If I change it
to "No" it records a value of 1,014 (again incorrect) it should 948.

Dave Peterson · May 21, 2009

Check your data.

Either your values aren't what you think they are or maybe you have hidden
rows???

Maybe you could add another column with a formula to help you find the
mismatches:

Insert a new column G and put this in G1:
=(f1="yes")&"--"&(d1>30000)&"--"&(d1<75000)
And drag down.

Now find one of the rows that you think should be counted and you should see
TRUE--TRUE--TRUE.

Your job will be to find the rows that aren't all true and determine why.

I'm betting either typing mistakes (extra characters in column F) or text that
looks like numbers in column D.

Dave Peterson · May 21, 2009

Check your data.

Either your values aren't what you think they are or maybe you have hidden
rows???

Maybe you could add another column with a formula to help you find the
mismatches:

Insert a new column G and put this in G1:
=(f1="yes")&"--"&(d1>30000)&"--"&(d1<75000)
And drag down.

Now find one of the rows that you think should be counted and you should see
TRUE--TRUE--TRUE.

Your job will be to find the rows that aren't all true and determine why.

I'm betting either typing mistakes (extra characters in column F) or text that
looks like numbers in column D.

Jacob Skaria · May 21, 2009

Great...

If this post helps click Yes

Jacob Skaria · May 21, 2009

Great...

If this post helps click Yes

Dave · May 21, 2009

Everything is working fine. Thanks!

Dave Peterson said:
Check your data.

Either your values aren't what you think they are or maybe you have hidden
rows???

Maybe you could add another column with a formula to help you find the
mismatches:

Insert a new column G and put this in G1:
=(f1="yes")&"--"&(d1>30000)&"--"&(d1<75000)
And drag down.

Now find one of the rows that you think should be counted and you should see
TRUE--TRUE--TRUE.

Your job will be to find the rows that aren't all true and determine why.

I'm betting either typing mistakes (extra characters in column F) or text that
looks like numbers in column D.

Dave · May 21, 2009

Everything is working fine. Thanks!

Dave Peterson said:
Check your data.

Either your values aren't what you think they are or maybe you have hidden
rows???

Maybe you could add another column with a formula to help you find the
mismatches:

Insert a new column G and put this in G1:
=(f1="yes")&"--"&(d1>30000)&"--"&(d1<75000)
And drag down.

Now find one of the rows that you think should be counted and you should see
TRUE--TRUE--TRUE.

Your job will be to find the rows that aren't all true and determine why.

I'm betting either typing mistakes (extra characters in column F) or text that
looks like numbers in column D.

Count cells with specific text between two dates?	5	Jan 28, 2010
count between a date range	9	Jun 2, 2010
COUNTIF Cells in Range?	4	Feb 22, 2010
COUNTIF and range?	6	Jul 22, 2005
Range Formula	7	Dec 12, 2007
format cells depending on value range	5	May 25, 2010
Excel Formula - Count Dates from another Sheet	8	Apr 27, 2018
Need to count a set of numbers in a range	4	Sep 11, 2009

COUNT BETWEEN A RANGE AND

Dave

Jacob Skaria

Jacob Skaria

Dave

Dave

Jacob Skaria

Jacob Skaria

Dave

Dave

Dave

Dave

Dave Peterson

Dave Peterson

Jacob Skaria

Jacob Skaria

Dave

Dave

Ask a Question

Similar Threads