| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif (maybe?)
In Col A i need formula to count how many HMOs in Col B have EPO in Col C
Col A Col B Col C 1 HMO EPO HMO UFO PPO ADP CEO EOP HOM TLC PPO EPO |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Countif (maybe?)
on cell A1
=SUM(($P$1:$P$1000=P1)*($Q$1:$Q$1000=Q1)) hit ctrl-shft-enter, after it will look like this {=SUM(($P$1:$P$1000=P1)*($Q$1:$Q$1000=Q1))} u can copy paste this cell downwards for all other criteria on same row. looks awkward for a count, maybe? "Alex" wrote: In Col A i need formula to count how many HMOs in Col B have EPO in Col C Col A Col B Col C 1 HMO EPO HMO UFO PPO ADP CEO EOP HOM TLC PPO EPO |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Countif (maybe?)
=SUMPRODUCT(--(B1:B100="HMO"),--(C1:C100="EPO"))
Commit with a simple ENTER (it is not an array formula) You cannot use whole columns (B:B) For more info see http://www.xldynamic.com/source/xld.SUMPRODUCT.html http://mcgimpsey.com/excel/formulae/doubleneg.html best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Alex" wrote in message ... In Col A i need formula to count how many HMOs in Col B have EPO in Col C Col A Col B Col C 1 HMO EPO HMO UFO PPO ADP CEO EOP HOM TLC PPO EPO |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Countif (maybe?)
opsss, a typo..my mistake
on cell A1 =SUM(($b$1:$b$1000=b1)*($c$1:$c$1000=Q1)) hit ctrl-shft-enter, after it will look like this {=SUM(($b$1:$b$1000=b1)*($c$1:$c$1000=c1))} u can copy paste this cell downwards for all other adjacent criteria on same row. looks awkward for a count, maybe? "Alex" wrote: In Col A i need formula to count how many HMOs in Col B have EPO in Col C Col A Col B Col C 1 HMO EPO HMO UFO PPO ADP CEO EOP HOM TLC PPO EPO |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Countif (maybe?)
I change the cell ranges to match my col.s and it looked like this
{=SUM(($B$3:$B$8=HMO)*($C$3:$C$8=EPO))} It gave me a #NAME? error... Any idea why? "driller" wrote: on cell A1 =SUM(($P$1:$P$1000=P1)*($Q$1:$Q$1000=Q1)) hit ctrl-shft-enter, after it will look like this {=SUM(($P$1:$P$1000=P1)*($Q$1:$Q$1000=Q1))} u can copy paste this cell downwards for all other criteria on same row. looks awkward for a count, maybe? "Alex" wrote: In Col A i need formula to count how many HMOs in Col B have EPO in Col C Col A Col B Col C 1 HMO EPO HMO UFO PPO ADP CEO EOP HOM TLC PPO EPO |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Countif (maybe?)
Worked great.. Thanks
"Bernard Liengme" wrote: =SUMPRODUCT(--(B1:B100="HMO"),--(C1:C100="EPO")) Commit with a simple ENTER (it is not an array formula) You cannot use whole columns (B:B) For more info see http://www.xldynamic.com/source/xld.SUMPRODUCT.html http://mcgimpsey.com/excel/formulae/doubleneg.html best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Alex" wrote in message ... In Col A i need formula to count how many HMOs in Col B have EPO in Col C Col A Col B Col C 1 HMO EPO HMO UFO PPO ADP CEO EOP HOM TLC PPO EPO |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Countif (maybe?)
this formula works bothways with cntrl-shft-enter
=SUM(($B$3:$B$8=cell ref1)*($C$3:$C$8=cell ref2)) this is a productive formula where u can place the text you like on any cell refs. =SUM(($B$3:$B$8="HMO")*($C$3:$C$8="EPO")) this is a specific formula - a one time use. "Alex" wrote: I change the cell ranges to match my col.s and it looked like this {=SUM(($B$3:$B$8=HMO)*($C$3:$C$8=EPO))} It gave me a #NAME? error... Any idea why? "driller" wrote: on cell A1 =SUM(($P$1:$P$1000=P1)*($Q$1:$Q$1000=Q1)) hit ctrl-shft-enter, after it will look like this {=SUM(($P$1:$P$1000=P1)*($Q$1:$Q$1000=Q1))} u can copy paste this cell downwards for all other criteria on same row. looks awkward for a count, maybe? "Alex" wrote: In Col A i need formula to count how many HMOs in Col B have EPO in Col C Col A Col B Col C 1 HMO EPO HMO UFO PPO ADP CEO EOP HOM TLC PPO EPO |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| sumproduct vs. countif | Excel Discussion (Misc queries) | |||
| Countif Formula /Sort Bug??? | Excel Discussion (Misc queries) | |||
| COUNTIF or not to COUNTIF on a range in another sheet | Excel Worksheet Functions | |||
| Combining IF and COUNTIF based on two columns | Excel Discussion (Misc queries) | |||
| Countif - Countif | Excel Worksheet Functions | |||
Linear Mode
