CODEWITHSUNDEEP
excelif-statementexcel-formula
Mike
Mike

Reputation: 1261

excel nested IF statements

I'm having some trouble with the below formula:

=IF(Data!X:X = 1, 
IF(Data!H:H = "Horse", 
IF(Data!U:U = A5, COUNT(Data!U:U)),0)

I need to check if column "X" in the excel sheet "Data" as the value of "1" if so, I need to check another column (in the same sheet) to see if it contains a particular text element(like: horse"), then I have to check to see if the column U in sheet "Data" contains the same value as my active sheet A5 if all the criteria match I need the count of how many times this occurs.

however my formula is only returning FALSE. I narrow it down to this part;

"IF(Data!H:H = "Horse")

now I double check , all the IF should end up as true.

Obviously I have something not right, any help would be great.

Upvotes: 2

Views: 329

Answers (2)

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35843

If you have Excel 2007 or later, you can use:

=COUNTIFs(Data!X:X, 1, Data!H:H, "Horse", Data!U:U , A5)

For Excel 2003:

=SUMPRODUCT((Data!X:X = 1)*(Data!H:H = "Horse")*(Data!U:U = A5))

Upvotes: 2

Chris Ballard
Chris Ballard

Reputation: 3769

Looks like the formula is incorrect (missing some of the false clauses in the if statements). This works for me:

=IF(Data!X:X = 1,
IF(Data!H:H = "Horse",
IF(Data!U:U = A5, COUNT(Data!U:U),0),0),0)

Upvotes: 1

Related Questions