COUNTIF to count rows with two conditions
 

I have a spreadsheet with 350 rows and two columns.

I want to count the number of rows which have BOTH the letter C in the first
column AND the letter Y in the second column.

I was thinking of using COUNTIF in a way similar to the following:

'=COUNTIF(G2:G350,"C")+COUNTIF(B2:B350,"Y")'

However, this counts the number of CELLS which contain EITHER of those two
letters. I want to count the number of ROWS with BOTH of those letters. I'm
guessing COUNTIF is the wrong command to use, but that I want something
similar to it.

Any suggestions would be much appreciated.

Thanks

COUNTIF to count rows with two conditions
 

try
=sumproduct((G2:G350="C")*(B2:B350="Y"))


--
Don Guillett
SalesAid Software

"Kaishain" wrote in message
...
I have a spreadsheet with 350 rows and two columns.

I want to count the number of rows which have BOTH the letter C in the
first
column AND the letter Y in the second column.

I was thinking of using COUNTIF in a way similar to the following:

'=COUNTIF(G2:G350,"C")+COUNTIF(B2:B350,"Y")'

However, this counts the number of CELLS which contain EITHER of those two
letters. I want to count the number of ROWS with BOTH of those letters.
I'm
guessing COUNTIF is the wrong command to use, but that I want something
similar to it.

Any suggestions would be much appreciated.

Thanks

COUNTIF to count rows with two conditions
 

This one works:

=SUMPRODUCT(--(G2:G350="C"),--(B2:B350="Y"))

"Kaishain" wrote:

I have a spreadsheet with 350 rows and two columns.

I want to count the number of rows which have BOTH the letter C in the first
column AND the letter Y in the second column.

I was thinking of using COUNTIF in a way similar to the following:

'=COUNTIF(G2:G350,"C")+COUNTIF(B2:B350,"Y")'

However, this counts the number of CELLS which contain EITHER of those two
letters. I want to count the number of ROWS with BOTH of those letters. I'm
guessing COUNTIF is the wrong command to use, but that I want something
similar to it.

Any suggestions would be much appreciated.

Thanks

COUNTIF to count rows with two conditions
 

Thanks for replying!

They both seem to work for me. What's the difference? Is either one better
for one reason or another?

Thanks again



"Byron720" wrote:

This one works:

=SUMPRODUCT(--(G2:G350="C"),--(B2:B350="Y"))

"Kaishain" wrote:

I have a spreadsheet with 350 rows and two columns.

I want to count the number of rows which have BOTH the letter C in the first
column AND the letter Y in the second column.

I was thinking of using COUNTIF in a way similar to the following:

'=COUNTIF(G2:G350,"C")+COUNTIF(B2:B350,"Y")'

However, this counts the number of CELLS which contain EITHER of those two
letters. I want to count the number of ROWS with BOTH of those letters. I'm
guessing COUNTIF is the wrong command to use, but that I want something
similar to it.

Any suggestions would be much appreciated.

Thanks

COUNTIF to count rows with two conditions
 

Hi

By and large there is no difference between the two methods.
In a few cases, one will work better than the other.

Sumproduct generally has its different terms separated by commas
=Sumproduct(term1,term2,term3).
In your case the terms are comparisons that will either result in a True
or False result.
These results need to be coerced from True to 1 and False to 0 in order
for Sumproduct to be able to produce a result.
The double unary minus -- is used to carry out the coercion.
The same could be achieved by adding 0 to the result or multiplying by 1
=SUMPRODUCT((G2:G350="C")*1,(B2:B350="Y")*1)

In the other case, the coercion is automatically taking place by using
the multiplication of the 2 terms within the Sumproduct, rather than
using a comma separator.

For a full discussion on this, Bob Phillips has an excellent treatise at
http://xldynamic.com/source/xld.SUMPRODUCT.html

--
Regards

Roger Govier


"Kaishain" wrote in message
...

Thanks for replying!

They both seem to work for me. What's the difference? Is either one
better
for one reason or another?

Thanks again



"Byron720" wrote:

This one works:

=SUMPRODUCT(--(G2:G350="C"),--(B2:B350="Y"))

"Kaishain" wrote:

I have a spreadsheet with 350 rows and two columns.

I want to count the number of rows which have BOTH the letter C in
the first
column AND the letter Y in the second column.

I was thinking of using COUNTIF in a way similar to the following:

'=COUNTIF(G2:G350,"C")+COUNTIF(B2:B350,"Y")'

However, this counts the number of CELLS which contain EITHER of
those two
letters. I want to count the number of ROWS with BOTH of those
letters. I'm
guessing COUNTIF is the wrong command to use, but that I want
something
similar to it.

Any suggestions would be much appreciated.

Thanks

COUNTIF to count rows with two conditions
 

Thanks again for the quick reply. It makes a lot more sense now.

However, I now have another problem:

There is a third column which contains different numbers (currency, in this
case). The formula used to count the rows returned 31 rows that met the
specified conditions. I now want to add the values of the third column of
each of those rows. I want something like '=SUM(C1:C350)', but only counting
the 31 rows found by the first formula.

Is it possible to do that?




"Roger Govier" wrote:

Hi

By and large there is no difference between the two methods.
In a few cases, one will work better than the other.

Sumproduct generally has its different terms separated by commas
=Sumproduct(term1,term2,term3).
In your case the terms are comparisons that will either result in a True
or False result.
These results need to be coerced from True to 1 and False to 0 in order
for Sumproduct to be able to produce a result.
The double unary minus -- is used to carry out the coercion.
The same could be achieved by adding 0 to the result or multiplying by 1
=SUMPRODUCT((G2:G350="C")*1,(B2:B350="Y")*1)

In the other case, the coercion is automatically taking place by using
the multiplication of the 2 terms within the Sumproduct, rather than
using a comma separator.

For a full discussion on this, Bob Phillips has an excellent treatise at
http://xldynamic.com/source/xld.SUMPRODUCT.html

--
Regards

Roger Govier


"Kaishain" wrote in message
...

Thanks for replying!

They both seem to work for me. What's the difference? Is either one
better
for one reason or another?

Thanks again



"Byron720" wrote:

This one works:

=SUMPRODUCT(--(G2:G350="C"),--(B2:B350="Y"))

"Kaishain" wrote:

I have a spreadsheet with 350 rows and two columns.

I want to count the number of rows which have BOTH the letter C in
the first
column AND the letter Y in the second column.

I was thinking of using COUNTIF in a way similar to the following:

'=COUNTIF(G2:G350,"C")+COUNTIF(B2:B350,"Y")'

However, this counts the number of CELLS which contain EITHER of
those two
letters. I want to count the number of ROWS with BOTH of those
letters. I'm
guessing COUNTIF is the wrong command to use, but that I want
something
similar to it.

Any suggestions would be much appreciated.

Thanks

COUNTIF to count rows with two conditions
 

Hi

=SUMPRODUCT((G2:G350="C")*(B2:B350="Y")*(C2:C350))
Strictly speaking, as they are just numbers in column C, they don't need
to be enclosed within another set of parentheses, but I usually find it
easier to always enclose each term in this way.


--
Regards

Roger Govier


"Kaishain" wrote in message
...
Thanks again for the quick reply. It makes a lot more sense now.

However, I now have another problem:

There is a third column which contains different numbers (currency, in
this
case). The formula used to count the rows returned 31 rows that met
the
specified conditions. I now want to add the values of the third
column of
each of those rows. I want something like '=SUM(C1:C350)', but only
counting
the 31 rows found by the first formula.

Is it possible to do that?




"Roger Govier" wrote:

Hi

By and large there is no difference between the two methods.
In a few cases, one will work better than the other.

Sumproduct generally has its different terms separated by commas
=Sumproduct(term1,term2,term3).
In your case the terms are comparisons that will either result in a
True
or False result.
These results need to be coerced from True to 1 and False to 0 in
order
for Sumproduct to be able to produce a result.
The double unary minus -- is used to carry out the coercion.
The same could be achieved by adding 0 to the result or multiplying
by 1
=SUMPRODUCT((G2:G350="C")*1,(B2:B350="Y")*1)

In the other case, the coercion is automatically taking place by
using
the multiplication of the 2 terms within the Sumproduct, rather than
using a comma separator.

For a full discussion on this, Bob Phillips has an excellent treatise
at
http://xldynamic.com/source/xld.SUMPRODUCT.html

--
Regards

Roger Govier


"Kaishain" wrote in message
...

Thanks for replying!

They both seem to work for me. What's the difference? Is either
one
better
for one reason or another?

Thanks again



"Byron720" wrote:

This one works:

=SUMPRODUCT(--(G2:G350="C"),--(B2:B350="Y"))

"Kaishain" wrote:

I have a spreadsheet with 350 rows and two columns.

I want to count the number of rows which have BOTH the letter C
in
the first
column AND the letter Y in the second column.

I was thinking of using COUNTIF in a way similar to the
following:

'=COUNTIF(G2:G350,"C")+COUNTIF(B2:B350,"Y")'

However, this counts the number of CELLS which contain EITHER of
those two
letters. I want to count the number of ROWS with BOTH of those
letters. I'm
guessing COUNTIF is the wrong command to use, but that I want
something
similar to it.

Any suggestions would be much appreciated.

Thanks

COUNTIF to count rows with two conditions
 

You've all been a great help! I think this has completely resolved my
problem now.

Thanks!



"Roger Govier" wrote:

Hi

=SUMPRODUCT((G2:G350="C")*(B2:B350="Y")*(C2:C350))
Strictly speaking, as they are just numbers in column C, they don't need
to be enclosed within another set of parentheses, but I usually find it
easier to always enclose each term in this way.


--
Regards

Roger Govier


"Kaishain" wrote in message
...
Thanks again for the quick reply. It makes a lot more sense now.

However, I now have another problem:

There is a third column which contains different numbers (currency, in
this
case). The formula used to count the rows returned 31 rows that met
the
specified conditions. I now want to add the values of the third
column of
each of those rows. I want something like '=SUM(C1:C350)', but only
counting
the 31 rows found by the first formula.

Is it possible to do that?




"Roger Govier" wrote:

Hi

By and large there is no difference between the two methods.
In a few cases, one will work better than the other.

Sumproduct generally has its different terms separated by commas
=Sumproduct(term1,term2,term3).
In your case the terms are comparisons that will either result in a
True
or False result.
These results need to be coerced from True to 1 and False to 0 in
order
for Sumproduct to be able to produce a result.
The double unary minus -- is used to carry out the coercion.
The same could be achieved by adding 0 to the result or multiplying
by 1
=SUMPRODUCT((G2:G350="C")*1,(B2:B350="Y")*1)

In the other case, the coercion is automatically taking place by
using
the multiplication of the 2 terms within the Sumproduct, rather than
using a comma separator.

For a full discussion on this, Bob Phillips has an excellent treatise
at
http://xldynamic.com/source/xld.SUMPRODUCT.html

--
Regards

Roger Govier


"Kaishain" wrote in message
...

Thanks for replying!

They both seem to work for me. What's the difference? Is either
one
better
for one reason or another?

Thanks again



"Byron720" wrote:

This one works:

=SUMPRODUCT(--(G2:G350="C"),--(B2:B350="Y"))

"Kaishain" wrote:

I have a spreadsheet with 350 rows and two columns.

I want to count the number of rows which have BOTH the letter C
in
the first
column AND the letter Y in the second column.

I was thinking of using COUNTIF in a way similar to the
following:

'=COUNTIF(G2:G350,"C")+COUNTIF(B2:B350,"Y")'

However, this counts the number of CELLS which contain EITHER of
those two
letters. I want to count the number of ROWS with BOTH of those
letters. I'm
guessing COUNTIF is the wrong command to use, but that I want
something
similar to it.

Any suggestions would be much appreciated.

Thanks