How to Count the rows meeting the criteria?

[gnaga]

Hi,

My data is spread into three columns and 100 rows. (A1:C100). I would like to count the rows based on the values in each column. Example as given below.

A B C
1 1 1 1
2 -1 -1 -1
3 1 -1 0
4 -1 0 1
5 1 1 1
.
.
.
100

For criteria = 1 count of rows = 2 (Row 1 & 5)
For criteria = -1 count of rows = 1 (Row 2)

How can I use the countif or sumif to get this?

 

[Phuoc]

1. For criteria = 1:

=COUNTIFS($A$1:$A$100,1,$B$1:$B$100,1,$C$1:$C$100,1)

2. For criteria = -1:

=COUNTIFS($A$1:$A$100,-1,$B$1:$B$100,-1,$C$1:$C$100,-1)

 

[gnaga]

Thanks Phuoc...It is working fine.

One more request - I would like to find only for either of two column combination (AB or BC or CA). How to add OR Condition?

 

[Phuoc]

Try this:

=SUMPRODUCT(--(($A$1:$A$100=1)+($B$1:$B$100=1)+($C$1:$C$100=1)>=2))

 

[gnaga]

Thank you so much. It works fine.