COUNTIF Function

T

Travers Ebling

Is it possible for COUNTIF to calculate two celll ranges
that meet two separate criteria? My closest, but
unsatisfactory attempt, looked like this:
=COUNTIF(PROFILE,"A"&IF(ORIENTATION,"LANDSCAPE")) it
returned a zero. I was hoping for a 2.

Profile is a NAMED cell range with entries such as A, B2,
C etc.

Orientation is a NAMED cell range with text entries such
as Portrait, Landscape etc.

I want Excel to count the number of occurences with "A"
and "Landscape" occuring in the same row.

I'm just starting to use nested funciton with Excel and
appreciate all suggestions for mastering this tool.

Cheers,
 
B

Bob Phillips

=SUMPRODUCT(--(PROFILE="A"),--(ORIENTATION="LANDSCAPE"))

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
A

Alan

COUNTIF will only find one value in a range, look at SUMIF or better still
SUMPRODUCT in Help
eg
if you had Ford, Subaru, Honda etc in column
1999, 2000,2001in colmn B
Red, Blue, Green in column C
Then
=SUMPRODUCT((A1:A100="Ford")*(B1:B100=2000)*(C1:C100="Blue"))
or as is fashionable
=SUMPRODUCT(--(A1:A100="Ford"),-(B1:B100=2000),-(C1:C100="Blue"))
will return all the Ford, 2000, Blue cars in the range A1:C100
You can pur 'Ford' etc into a cell and use that as the search value too
Regards,
 
Top