IF statements with text and numbers across multiple tabs

**greeneggsandsam** · 02-28-2013, 12:53 PM

I am working on a sheet where in TAB 1 I have the following formula.......
=IF(L4>0,(L4-K4)+(J4-I4)+(H4-G4), "PENDING") in column N
There are approximately 8 rows with this formula (Rows N4 - N12) and they are based on dates in and out through various departments.

On TAB 2 I want to summarize the data from TAB 1 so that if any of the data in the range N4:N30 = "PENDING" then the cell will display a 0 if True and 1 if False as below.....
=IF('TAB 2'!N4:N12="PENDING",0,1)
The issue is it keeps returning a 1 indicating false when Cell N9 does have a "PENDING" value

Thanks in advance for all your help

**BB1972** · 02-28-2013, 03:00 PM

Can you upload a sample workbook (with any sensitive data removed) for us to work on?

**greeneggsandsam** · 02-28-2013, 03:23 PM

Here you go
There is some conditional formatting applied on the TEST tab (yellow and red shading)
Cell G4 of the SUMMARY Tab is where I have the formula I am trying to make work.
Thanks again for all your help.

**BB1972** · 02-28-2013, 04:05 PM

To be honest, I'm not sure why the formula isn't working; however, if you have a look at the attached, I have used the following in G5:

Please Login or Register  to view this content.

which does as you require. How is this for you?

**greeneggsandsam** · 02-28-2013, 05:32 PM

Thanks for the help.
How many arguments can I have in that formula?
There are times where the number of W.O.'s generated can be as high as 50-60.
Thanks again for your help.

**BB1972** · 03-02-2013, 10:15 AM

You're welcome - glad to help, and thanks for the rep.

From Excel's built-in help function: Up to 64 IF functions can be nested as value_if_true and value_if_false arguments to construct more elaborate tests. ... Alternatively, to test many conditions, consider using the LOOKUP, VLOOKUP, HLOOKUP, or CHOOSE functions.