Nested IFs syntax or better solution

[mart10]

i am using excel 2010

I have data as follows:

col a colb volc col d TEST col
12345 abc uts CHECK
123 34567 ytx CHECK
12345 3456 uy34 869 OK

What I want to do is a check cloumn flag for all cells with NULL values I pressume I do this be checking each column with an IF and then somehow nesting the IFs. But I am unsure on the syntax

Or is there an even neater way and testing a whole block of data

 

[macropod]

There is nothing about your data to indicate where any NULL values might exit. You have two rows with 'CHECK' in what appears to be column D and one with 'OK' in column E, but how these might relate to your NULL values is far from clear.

Cheers
Paul Edstein
[MS MVP - Word]

 

[mart10]

Yes , sorry before I posted it looked clear and then squashed up on posting! The nulls can come anywhere in columns a to d, in fact i have about 10 columns of data

I was given the solution to do a CountA on all the rows and then just filter f not equal to 10. this works but not that flexible:
I may want to include fisrt 2 columns, then misss a column etc

 

[macropod]

To check a row for Nulls, you could use:
=COUNTIF(A1:J1,"")
To check a column for Nulls, you could use:
=COUNTIF(A1:A10,"")
To check a range for Nulls, you could use:
=COUNTIF(A1:J10,"")
The result equals the count of Nulls (i.e. empty cells)

As for:

I may want to include fisrt 2 columns, then misss a column etc

quite clearly, you'd have to tailor the formulae for your criteria.

Cheers
Paul Edstein
[MS MVP - Word]

 

[Andrzejek]

Is thet what you are trying to show as your example:

[pre]
A B C D E
12345 abc uts CHECK <NULL>
123 34567 ytx CHECK <NULL>
12345 3456 uy34 869 OK
[/pre]

If so, use [pre] tag to align the data.


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[zelgar]

Since NULL is a blank cells, you can use the COUNTBLANK command...

To check a row for Nulls, you could use:
=COUNTBlank(A1:J1)
To check a column for Nulls, you could use:
=COUNTBLANK(A1:A10)
To check a range for Nulls, you could use:
=COUNTBLANK(A1:J10)
The result equals the count of Nulls (i.e. empty cells)

Now, if you are wanting to count only those rows where all of the data is blank in the row, then a COUNTIFS command would work. The following would indicate the work for Cols A:E.

=COUNTIFS(A1:A10,"",B1:B10,"",C1:C10,"",D110,"",E1:E10,"")