how to count a column based on condition of another column
 

Column E: class of doctors. Includes A,B,C
Column L: residing city of doctors

I want to count number of doctors with class A in a specified city.
I thought the following formula will work:
IF(L10:L93="Tabriz",COUNTIF(E10:E93,"A"),) but it didn't.
Basically I am looking for a foormula to count a cloumn when a condition is
met in another column.
How can I write this formula?

how to count a column based on condition of another column
 

=SUMPRODUCT(--(L10:L93="Tabriz"),--(E10:E93="A"))

--
Regards,
Dave


"R Khoshravan" wrote:

Column E: class of doctors. Includes A,B,C
Column L: residing city of doctors

I want to count number of doctors with class A in a specified city.
I thought the following formula will work:
IF(L10:L93="Tabriz",COUNTIF(E10:E93,"A"),) but it didn't.
Basically I am looking for a foormula to count a cloumn when a condition is
met in another column.
How can I write this formula?

how to count a column based on condition of another column
 

Hi David,

This is something I need to learn.
Why does it work with the --?
Isn't that like a double negative? Why doesn't it work without it?
Where can I find out more about this?

thanks for your help
--
Allllen


"David Billigmeier" wrote:

=SUMPRODUCT(--(L10:L93="Tabriz"),--(E10:E93="A"))

--
Regards,
Dave


"R Khoshravan" wrote:

Column E: class of doctors. Includes A,B,C
Column L: residing city of doctors

I want to count number of doctors with class A in a specified city.
I thought the following formula will work:
IF(L10:L93="Tabriz",COUNTIF(E10:E93,"A"),) but it didn't.
Basically I am looking for a foormula to count a cloumn when a condition is
met in another column.
How can I write this formula?

how to count a column based on condition of another column
 

The double negative converts TRUE/FALSE to 1/0. The reason it doesn't work
without them is Excel doesn't understand a summation of TRUE's and FALSE's.
You can also achieve this by doing any kind of mathematical computation to
the ranges, for example both of these will work as well:

=SUMPRODUCT(1*(L10:L93="Tabriz"),1*(E10:E93="A"))
=SUMPRODUCT((L10:L93="Tabriz")*(E10:E93="A"))

You can learn more about SUMPRODUCT() he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html


--
Regards,
Dave


"Allllen" wrote:

Hi David,

This is something I need to learn.
Why does it work with the --?
Isn't that like a double negative? Why doesn't it work without it?
Where can I find out more about this?

thanks for your help
--
Allllen


"David Billigmeier" wrote:

=SUMPRODUCT(--(L10:L93="Tabriz"),--(E10:E93="A"))

--
Regards,
Dave


"R Khoshravan" wrote:

Column E: class of doctors. Includes A,B,C
Column L: residing city of doctors

I want to count number of doctors with class A in a specified city.
I thought the following formula will work:
IF(L10:L93="Tabriz",COUNTIF(E10:E93,"A"),) but it didn't.
Basically I am looking for a foormula to count a cloumn when a condition is
met in another column.
How can I write this formula?

how to count a column based on condition of another column
 

Brilliant. Thanks very much.
--
Allllen


"David Billigmeier" wrote:

The double negative converts TRUE/FALSE to 1/0. The reason it doesn't work
without them is Excel doesn't understand a summation of TRUE's and FALSE's.
You can also achieve this by doing any kind of mathematical computation to
the ranges, for example both of these will work as well:

=SUMPRODUCT(1*(L10:L93="Tabriz"),1*(E10:E93="A"))
=SUMPRODUCT((L10:L93="Tabriz")*(E10:E93="A"))

You can learn more about SUMPRODUCT() he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html


--
Regards,
Dave


"Allllen" wrote:

Hi David,

This is something I need to learn.
Why does it work with the --?
Isn't that like a double negative? Why doesn't it work without it?
Where can I find out more about this?

thanks for your help
--
Allllen


"David Billigmeier" wrote:

=SUMPRODUCT(--(L10:L93="Tabriz"),--(E10:E93="A"))

--
Regards,
Dave


"R Khoshravan" wrote:

Column E: class of doctors. Includes A,B,C
Column L: residing city of doctors

I want to count number of doctors with class A in a specified city.
I thought the following formula will work:
IF(L10:L93="Tabriz",COUNTIF(E10:E93,"A"),) but it didn't.
Basically I am looking for a foormula to count a cloumn when a condition is
met in another column.
How can I write this formula?