| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| I need to count data in one colum based on data in another column | Excel Worksheet Functions | |||
| count values in one column based on their realtionship with anoth. | Excel Worksheet Functions | |||
| Count Intervals of 1 Numeric value in a Row and Return Count down Column | Excel Worksheet Functions | |||
| Count Position of Filtered TEXT cells in a column | Excel Worksheet Functions | |||
| How do I hide a column based on a condition (option not available. | Excel Worksheet Functions | |||
Linear Mode
