IF formula for a range almost works, except with blank cells [Solved/Closed]

Report
Posts
2
Registration date
Thursday June 18, 2015
Status
Member
Last seen
June 19, 2015
-
Posts
2
Registration date
Thursday June 18, 2015
Status
Member
Last seen
June 19, 2015
-
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: https://www.mrexcel.com/board/threads/if-formula-for-a-range-almost-works-except-with-blank-cells-win7-excel-2010.862406/#post4188823

I'm using Win7 Excel 2010. 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.

3 replies

Posts
1004
Registration date
Saturday January 17, 2015
Status
Moderator
Last seen
November 27, 2020
119
Hi eddiecruz,

Im certainly not an excel expert, but if I were doing this I would try a nested "IF" statement - something like this ...

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

I'm sure there is a better way, but it worked on my machine.
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Posts
1004
Registration date
Saturday January 17, 2015
Status
Moderator
Last seen
November 27, 2020
119
Eddiecruz says that he found a solution on the other forum ... and didnt bother to post the solution here. HOW RUDE!

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


... much better than mine :^)

I really appreciate thank you messages as a payment for solving issues :o)
Posts
2
Registration date
Thursday June 18, 2015
Status
Member
Last seen
June 19, 2015

All apologies. No offense intended. I needed to rush out of the office and did not want folks crunching code on my behalf unnecessarily. I'll do better next time.

My solution is hardly my own. I cobbled it together from pieces I found all over the place. It seems to work, but if I can get a solid understanding of WHY it works, I'll be a bit further on my way to having been taught to fish and feeding myself for a lifetime.

Your suggestion actually helps quite a lot. I tried nested IF statements but could not get them to work. I see now that at least part of the problem was that I thought each IF had to be enclosed in parentheses in situ rather than all at the end. So thanks very much, BrianGreen!

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!