Countif (maybe?)

A

Alex

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
 
D

driller

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?
 
D

driller

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?
 
A

Alex

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?
 
D

driller

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.
 
Top