Countif depending on the conditions across multiple columns

S

san

How can I count the no. of rows in a sheet depending on the values in 2
columns ?

Eg:
Suppose I have two columns in an excel sheet viz

ColA ColB
1 1
2 1
1 2
2 1
1 2
2 0
2 1


I want to count the nos. of 1's in colB iif value in colA for the same row
is "2" ?
I dont want to use a filter.

Result for above example should be:
The count result for the above example should be 3 due to following 3 rows
ColA ColB
2 1
2 1
2 1
 
V

vsoler

How can I count the no. of rows in a sheet depending on the values in  2
columns ?

Eg:
Suppose I have two columns in an excel sheet viz

ColA   ColB
1       1
2       1
1       2
2       1
1       2
2       0
2       1

I want to count the nos. of 1's in colB iif value in colA for the same row
is "2" ?
I dont want to use a filter.

Result for above example should be:
The count result for the above example should be 3 due to following 3 rows
ColA   ColB
2       1
2       1
2       1

Use SUMPRODUCT:

=SUMPRODUCT((A1:A7=2)*(B1:B7=1))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top