How do I count ???

  • Thread starter Thread starter bsitrus
  • Start date Start date
B

bsitrus

Hi, please help me.

I have a 2 columns

column 1 displays Dollar Amounts
column 2 displays Numbers

I need to Count how many entries have a Dollar Amount > 0 and Number >
5

To count dollar > 0 is easy, COUNTIF(RANGE,">0")

To count Number > 5 is just as easy COUNTIF(RANGE2,">5")

But how do I combine the two. Basically, I need a count of how many
entries are above 0 dollars and above 5 Number


So:

$20 3
$-2 7
$5 6
$2 3
$1 9

It displays 2 ???

Thanks in advance
 
bsitrus,

I am going to assume $ in Col A and Values in Col B.

Solution A

=SUMPRODUCT((A1:A5>0)*(B1:B5>5))

Or you can use an array entered formula

Solution B

=SUM((A1:A5>0)*(B1:B5>5))

Use control shift enter to create an array entered formula. When you are
finished it will look like (XL puts in the brackets automatically)

{=SUM((A1:A5>0)*(B1:B5>5))}

Regards,
Kevin
 
Thanks Kev,

but thats not what I was asking.

I was asking on how to count the number of entries (or pairs in this
case), where col A would be >0 AND col B would be >5


I attached a spreadsheet that better illustrates the question.


Any help will be greatly appriciated!!!

Thanks


+----------------------------------------------------------------+
| Attachment filename: counting.xls |
|Download attachment: http://www.excelforum.com/attachment.php?postid=350554|
+----------------------------------------------------------------+
 
bsitrus,

I got your spreadsheet. There are 3 instances where values in A are >0 and
values in B are >5. Correct? This occurs on rows 1, 5 and 6?

This formula answer that question.

=SUMPRODUCT((A1:A14>0)*(B1:B14>5))

And so does this formula

=SUM((A1:A14>0)*(B1:B14>5)) when it is array entered, control shift enter
so that it looks like
{=SUM((A1:A14>0)*(B1:B14>5))}

Let me explain the formula so that you will see that it does in fact answer
your question.

Here are your values for A1, B1....$8024.43, 19

Is A1 >0, True so it is 1
Is B1 > 5, True so it is 1

1*1=1

look at A2, B2....$$2,520.80, 3

Is A1 >0, True so it is 1
Is B1 > 5, False so it is 0

1*0=0

Go through each row and add the "1s" together to get 3 (rows 1, 5, and 6).

So we add up all the instances where BOTH Col A and Col B criteria are
satisfied. In other words, the "Number of Pairs that are A>0 AND B>5" are
added using either of the two formulas above.

Review this information and I believe it answers your question in your
spreadsheet perfectly. Put the formula in your spreadsheet and change your
values to ensure that it meets your needs.

HTH

Regards,
Kevin
 
Back
Top