CountIF Statement combining 2 different pieces of data
 

I have a spreadsheet with many columns, the two I need to use are Tier and
Type -
I am trying to write a COUNTIF statement that finds a single tier such as
"US Regional: and find two types i.e. ("Airline" and "R4"). I want to find
all the entries
for "US Regional" that contain both the "airline" and "R4" types.

My Example =COUNTIF(K1:K78, "US Regional") AND COUNTIF(G1:G78, "Airline").

Explanation: I may have 12 entries for US Regional and 1 entry for
"Airline" and 8 entries for "R4". So I would like to see 9 lines showing up,
US Regional, Airline
and 8 US Regional entries and 8 entries for "R4".

CountIF Statement combining 2 different pieces of data
 

If you are using Excel 2007, try using COUNTIFS. I believe you'll need

=COUNTIFS(K1:K78, "US Regional",G1:G78, "Airline")

If in Excel 2003 (could also use in 2007) , you'll need to use SUMPRODUCT

=SUMPRODUCT(--(K1:K78 = "US Regional"),--(G1:G78= "Airline"))


--
HTH,

Barb Reinhardt



"NormaD" wrote:

I have a spreadsheet with many columns, the two I need to use are Tier and
Type -
I am trying to write a COUNTIF statement that finds a single tier such as
"US Regional: and find two types i.e. ("Airline" and "R4"). I want to find
all the entries
for "US Regional" that contain both the "airline" and "R4" types.

My Example =COUNTIF(K1:K78, "US Regional") AND COUNTIF(G1:G78, "Airline").

Explanation: I may have 12 entries for US Regional and 1 entry for
"Airline" and 8 entries for "R4". So I would like to see 9 lines showing up,
US Regional, Airline
and 8 US Regional entries and 8 entries for "R4".