sum and count multiple conditions

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ftahbaz@gmail.com - 30 Nov 2006 18:13 GMT

I have a sheet with the folliwing info: the "///" represents a new
column

Sheet 1

A///12
B///10
D///-5
C///-1
E///0
G///6
A///8
B///1
D///9
E///7

Now I know that I can use the formula =countif(Sheet1!A:A,"A") and get
the number of occurences that A had...
Also, I can get the sum of all the A occurences with
=sumif(Sheet1!A:A,"A",Sheet1!B:B)

What I want to know is...what formula would count the number of times
that A was positive...and also what formula to only sum up the times
that A was positive.

Thanks a lot

Reply to this Message

Roger Govier - 30 Nov 2006 18:22 GMT

Hi

How do you want to treat 0 in your count of positive's, include or
ignore?
Including, I have used >=0 in the following formula. To exclude from the
count just use >0

Count
=SUMPRODUCT($A$1:$A$100="A")*($B$1:$B$100>=0))

Sum
=SUMPRODUCT($A$1:$A$100="A")*($B$1:$B$100>=0)*$B$1:$B$100))
Clearly in the Sum, it does not alter the result whether 0 is included
or excluded.

Signature

Regards

Roger Govier

>I have a sheet with the folliwing info: the "///" represents a new
> column
[quoted text clipped - 22 lines]
>
> Thanks a lot

Reply to this Message

Bob Phillips - 30 Nov 2006 18:26 GMT

=COUNTIF(A:A,">0")

=SUMIF(A:A,">0")

Signature

HTH

Bob

(change the xxxx to gmail if mailing direct)

>I have a sheet with the folliwing info: the "///" represents a new
> column
[quoted text clipped - 22 lines]
>
> Thanks a lot

Reply to this Message

Rate this thread: