excel nested IF statements

Question

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.

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
– Dmitry Pavliv, Commented Mar 13, 2014 at 17:19
maybe something like this: =SUMPRODUCT((Data!X:X = 1)*(Data!H:H = "Horse")*(Data!U:U = A5))? — Dmitry Pavliv
– Dmitry Pavliv, Commented Mar 13, 2014 at 17:22
That worked! SUMPRODUCT if you put this as a answer I will credit you the score — Mike
– Mike, Commented Mar 13, 2014 at 17:46

Dmitry Pavliv · Accepted Answer · 2014-03-13 17:47:06Z

2

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

answered Mar 13, 2014 at 17:47

Dmitry Pavliv

35.9k13 gold badges84 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Chris Ballard · Accepted Answer · 2014-03-13 17:17:42Z

1

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)

answered Mar 13, 2014 at 17:17

Chris Ballard

3,7684 gold badges31 silver badges40 bronze badges

1 Comment

Mike Over a year ago

Thank Chris but I still have the same issue, it looks like SUMPRODUCT was the solution to my issue, thanks again!

Collectives™ on Stack Overflow

excel nested IF statements

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related