excel nested IF statements

2
votes

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.

2
excelif-statementexcel-formula
I need to check if column "X" in the excel sheet "Data" as the value of "1" - not sure what do you mean. You need to check whether column X contains value 1? - Dmitry Pavliv
maybe something like this: =SUMPRODUCT((Data!X:X = 1)*(Data!H:H = "Horse")*(Data!U:U = A5))? - Dmitry Pavliv
That worked! SUMPRODUCT if you put this as a answer I will credit you the score - Mike

2 Answers

2
votes

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))
1
votes

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)