COUNTIFS 3rd Criteria not being Applied

[falkon007]

Dear Excel Geniuses,

I am having an issue with COUNTIFS in Excel 2010.

I have 1524 rows of data with one coloumn containing a set of repeating numbers representing "Flight Numbers". Then next coloumn states what trip dates it was e.g. 276 would be the 27th Saturday (6th day) and a thrid coloumn either has a time in seconds if it was successful or blank if the calculation failed.

When i use:

=COUNTIFS($E$4:$E$1524,K34,$F$4:$F$1524,$N$3)

This shows 20. Which means in the entire data set there were 20 "FLIGHT NUMBERS" which matched cell K23 on the "TRIP DATE" that matches N3. So far so good.

Now When I use:

=COUNTIFS($E$4:$E$1524,K34,$F$4:$F$1524,$N$3,$I$4:$I$1524,"<>"&"")

This still shows 20. When I actually look at the data manually. All 20 occurences of the Flight Matching Cell K34 on TRIP date matching N3, Coloumn "I" is blank for each one and hence should show a zero...not 20.

What am I doing wrong?

Additional information: Coloumn I was calculcated using a formula:

=IF(OR(H4="TRUE",G4="FAILED"),"",G4)..... so when it is blank it was derived to be as such using ""

Please help.

Is there anyway I can make the thrid criteria be met?

Kind Regards,

[martindwilson]

try just
=COUNTIFS($E$4:$E$1524,K34,$F$4:$F$1524,$N$3,$I$4:$I$1524,"<>")
ah that wont work either try sumproduct instead

=SUMPRODUCT(($E$4:$E$1524=K34)*($F$4:$F$1524=$N$3)*($I$4:$I$1524<>"") )

[falkon007]

Thank you martindwilson but that returns a #VALUE! error.... I do have instances of Text in coloumns E and Coloumn F...could this be an issue?

[falkon007]

Are there any other solutions or better yet an explanation on how to make the thrid criteria of COUNTIFS be utilised?

[benishiryo]

hi falkon007. yes unfortunately, COUNTIF will count those cells with formulas you indicated as "". try this instead:
=COUNTIFS($E$4:$E$1524,K34,$F$4:$F$1524,$N$3,$I$4:$I$1524,">""")

[martindwilson]

=SUMPRODUCT(($E$4:$E$1524=K34)*($F$4:$F$1524=$N$3)*($I$4:$I$1524<>"") ) does not return an error you must have input it incorrectly