Counting with ranges

**chemmiah** · 10-19-2011, 11:18 AM

Hello everyone,

I am trying to calculate the number of entries within certain criteria.

For example:
Column A Column B
1.771277978 1.49972
5.832262498 1.0647
6.350262937 1.88901
6.940459075 3.14165
6.596007294 1.99719
4.288934835 3.0043
0.224337565 -0.29771
2.978691222 1.636476
5.870144774 1.83775
2.28300368 -2.24976
3.550696473 0.400509
3.498860598 1.30073

The list of number (it's trunkated) i need to calculated the number of points were A>5 and b not negative, also 5>A>4 and B is not negative, and so on.

**NBVC** · 10-19-2011, 11:29 AM

If you have XL2007 or later:

=COUNTIFS(A1:A100,">5",B1:B100,">=0")

and

=COUNTIFS(A1:A100,">4",A1:A100,"<5",B1:B100,">=0")

if using XL2003 or earlier

=SUMPRODUCT(--(A1:A100>5),--(B1:B100>=0))

and

=SUMPRODUCT(--(A1:A100>4),--(A1:A100<5),--(B1:B100>=0))

continue those patterns.

Note: What about equals 5... add = where appropriate.

**chemmiah** · 10-19-2011, 11:33 AM

Thanks it worked!!!!

**NBVC** · 10-19-2011, 11:39 AM

Please update your profile to indicate your Office version and mark thread as solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

Counting with ranges

LinkBack

Thread Tools

Rate This Thread

Display

Counting with ranges

Re: sumproduct?

Re: sumproduct?

Re: sumproduct?

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions