How do I count ???

[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

 

[Kevin Stecyk]

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

 

[bsitrus]

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|
+----------------------------------------------------------------+

 

[Kevin Stecyk]

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