IF formula for a range almost works, except with blank cells [Win7 Excel 2010]

[eddiecruz]

Apologies if the title is too vague. I will if necessary rewrite it to comply with forum rules.

I have posted this same question here: IF formula for a range almost works, except with blank cells

The following formula (in cell H2) works to returns today's date when the cells in the range contain text, and returns nothing when there is a mix of text and numbers:

=IF(SUMPRODUCT(ISNUMBER(C2:G2)*1)>0,"",TODAY())

In the cells in that range, I enter [same], [none], [removed], [new record], or the date information was updated. If all 5 cells contain text, H2 should show the date the data was entered; if one of them contains a date, this is not necessary. My goal is this: H2 should be blank if the range contains text or any blank cells; otherwise, it should return today's date.

The problem is this: The formula also returns today's date if there are one or more blank cells in the range. So, how to ignore blank cells?

Probably a simple thing, and I've found several examples for A blank cell but not for blank cellS. I much appreciate your help and patience. My questions, I promise, will become less ignorant with time and medication. Thanks very much.

 

[oldbrewer]

My goal is this: H2 should be blank if the range contains text or any blank cells; otherwise, it should return today's date.

so if any cell is blank, or any cell contains text, return "blank", if not return today's date

maybe =isnumber(C2) + isnumber(C3) etc so if total = 5 all cells are numbers

less than 5 and at least one cell is blank or text

 

[Asala42]

Try this - this appears to test well:

=IF(SUMPRODUCT(--(ISNUMBER(C2:G2)=FALSE))>0,"",TODAY())

 

[eddiecruz]

so if any cell is blank, or any cell contains text, return "blank", if not return today's date

maybe =isnumber(C2) + isnumber(C3) etc so if total = 5 all cells are numbers

less than 5 and at least one cell is blank or text

That's a whole different approach (at least to me it seems like a whole different approach) and much simpler. I played with it for a while and couldn't get it to work. I'd either get all TODAY() or all "blank." Maybe I'm even more beginner than I thought. Thank you for taking the time to reply to a question from a new stranger.

 

[eddiecruz]

Try this - this appears to test well:

=IF(SUMPRODUCT(--(ISNUMBER(C2:G2)=FALSE))>0,"",TODAY())

Can I just say a double unary is scary?

I tried your suggestion exactly as is. This screenshot shows my result. https://drive.google.com/file/d/0Bw4-qm7yhG-9TTZDdl9GbkNJQzg/view?usp=sharing

I tried to place different data in each from, all dates, all blank, some text and some dates, etc... In the quite likely event I was not able to upload said screenshot, all rows returned blank except for the all dates row, which returned TODAY(). Thanks very much. I'll keep working on it.

 

[eddiecruz]

I think I got it! I accidentally, I confess, discovered ISNONTEXT and came up with this, which seems to work:

=IF(SUMPRODUCT(ISNONTEXT(C2:G2)*1)>0,"",TODAY())

I've marked it as SOLVED in the other forum, but here I'd love one of you pros to sign off on it. Thanks! (Yes, I'm inappropriately excited, sorry.)

 

[Asala42]

Ah I think I misread your original objective.

In testing your formula:
- H2 will show the date if all cells in C2:G2 contain text
- H2 will show blank if any of those cells are blank or numeric

 

[eddiecruz]

Thank you very very much for your help. I suspect I will be spending MUCHO time here learning and look forward to it. (I cannot, by the way, figure out how to "close" this thread or mark it as solved. I'm searching...)