'COUNTIF' and 'AND'

[Rajula]

Hi,

Can i use COUNTIF with AND funtion? My problem

B P
FTE Architect
FTE Architect
Contract BA
FTE BA
Contract BA

I want the count of people who are FTE and Architect, and people who are FTE
and BA etc etc......

Help appreciated..
Regards
Rajula

 

[Don Guillett]

try
=SUMPRODUCT((b2:b222="fte")*(p2:b222={"architect","ba"}))

 

[Bernard Liengme]

Hello Rajula:
=SUMPRODUCT(--(B1:B500="FTE"), --(P1100="Architect))
Notes:
a) do not use total columns as in
=SUMPRODUCT(--(B:B="FTE"), --(P="Architect))
as array formulas cannot cope with this
b) for explanations see
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
http://mcgimpsey.com/excel/formulae/doubleneg.html

best wishes

 

[Ardus Petus]

=SUMPRODUCT((B1:B999="FTE")*(P1999="Architect"))

HTH

 

[Bob Phillips]

=SUMPRODUCT(--(B2:B20="FTE"),--(P220="Architect"))

etc.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Toppers]

=sumproduct(--(B1:B100="FTE"),--(P1100="Architect"))

Change ranges to suit but cannnot be a column i.e. B:B is not allowed

or

=sumproduct(--(B1:B100=X1),--(P1100=X2))

where X1="FTE" and X2="Architect"

 

[Rajula]

From the excel help, i could understand that sumproduct function is used for
suming the products of numbers.

I want a COUNT. In my eg. I have 2 people who are FTEs and Architects. The
same way i have 2 people who are contract and Architect.. etc..

 

[Rajula]

Hi,

Thanks to all the replies..
BOB your formula works perfectly.

Thanks...