Countif (maybe?)

[Guest]

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

 

[Guest]

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?

 

[Bernard Liengme]

=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

 

[Guest]

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?

 

[Guest]

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?

 

[Guest]

Worked great.. Thanks

=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

 

[Guest]

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.