Multiple column status - Formula

[Linraen7]

Hiya,

I am not Pro with Formulas but I want to maintain a file for all the details about employee documents submitted.

Eg. Column A to Column F has all the required document name viz.. Photo ID proof, Address Proof etc.

The Final column "G" I have to Manually enter "Incomplete" if any of the cells are empty.

I tried the IF & Then Formula (after a lot research on this site) but it works fine until the first 3 columns and then I get confused myself :P

Is there a shorter way to simply check value of each column and if any of the column between A to F is empty "Blank" then I get a msg "Incomplete" in the Final Column.

Any Help will be appreciated.
Thanks
Lin

[benishiryo]

hi Lin. assuming data is in A1:F1, try:
=IF(COUNTA(A1:F1)<>6,"Incomplete","")

so if it's all filled up, it will show a blank.

[Marcol]

Maybe

Please Login or Register  to view this content.

[Marcol]

@ benishiryo

Be careful with COUNTA(),
If the cell value is formula derived and the result is "", then COUNTA() will count that cell.

See this workbook

[benishiryo]

@Marcol

yeah, oversight on my part. thanks for the heads up

[Linraen7]

@ Marcol & @ benishiryo,

Thank you for the formula, it worked...but... I have a column of "Date" in the row.

This formula doesn't work over there. Can you please guide further.

Here's the attachment...

Lin

[Marcol]

Ahhh ... the joys of Excel!

Excel treats numbers as if they are less than "", see row 2 in this example sheet, and text as being greater than "".

COUNTA() and COUNTIF() can both return unexpected results.

1/. COUNTA() counts all cells that are not null, however it will include any cell with a formula that returns "" See Column W, the grey cells in the table A4:N10 contain formulae.

2/.COUNTIF() If the criteria is ">""" text will be counted, excluding blanks (Col S), so far so good.

So you might reasonably expect "<""" to count numbers ... Wrong! It counts blanks returned by formulae.(Col T)

How do you get it to count numbers?
">=0" counts positive numbers and zeros, "<0" counts negative numbers, so add them together, or use "<"&99^99, but this is pointless because COUNT() does the business.

So COUNTIF() counts if the stated criteria is TRUE, numbers, as such, are a problem.

3/. It is often stated - "Use COUNTIF/S() rather than SUMPRODUCT()"
this works for this example sheet

Please Login or Register  to view this content.

This looks like an alternative, but isn't!

Please Login or Register  to view this content.

Summing up, if your worksheet doesn't have cells with formulae that return "" use this ...

Please Login or Register  to view this content.

Otherwise use the SUMPRODUCT formula above.

Rant over! ...

[Linraen7]

Whoa... that was too much for my brain :P ... but it indeed solved my query.. Thank You @ Marcol.

The COUNTIF formula was the one that worked for my requirement.

Also I learned many new terms in excel.. all thanks to your patient explanation that you provided.

Thanks,
Lin