sum and count multiple conditions

[ftahbaz]

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

 

[Roger Govier]

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.

 

[Bob Phillips]

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

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

--
HTH

Bob

(change the xxxx to gmail if mailing direct)