How to get a formula to ignore blank cells in an = to function, NOT averages

[Karrie]

I think this should be a really simple problem for you guys, but I'
tearing my hair out!

I have a formula that reads:

=IF(E3=F3,1,0)+IF(F11=E11,1,0)+IF(E19=F19,1,0)

Simple enough, BUT what I don't want it to do is count any of the cell
if they are blank. i.e. if cells E3 and F3 are both blank, I want it t
return 0 NOT 1, and so on.

HELPPPPPPPPPPPPP!!

 

[David McRitchie]

Hi Karrie,

=AND(NOT(ISBLANK(E3),E3=F3)+AND(not(ISBLANK(e11),E11=F11)+AND((NOT(ISBLANK(e19),E19=F19) )

 

[Peo Sjoblom]

This would work

=(E3=F3)*(E3<>"")+(E11=F11)*(E11<>"")+(E19=F19)*(E19<>"")

--


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

 

[Karrie]

Oh my goodness that worked! Thank you so much Peo! I really appreciat
it!

ps: Am I not posting to the Newsgroup? I don't understand what I'
doing wrong there

 

[Peo Sjoblom]

Oh my goodness that worked! Thank you so much Peo! I really appreciate
it!

ps: Am I not posting to the Newsgroup? I don't understand what I'm
doing wrong there!

Well, you managed to thank me while posting it as an answer to David's post
<bg>
If you were asking about my signature it means that I prefer not to have
personal
emails sent to me asking questions but rather have any follow-ups in the
newsgroups or
the excel forum which you seem to use.
--


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

 

[Arvi Laanemets]

Hi

=(E3=F3)*(E3<>"")+(E11=F11)*(E11<>"")+(E19=F19)*(E19<>"")

 

[Karrie]

Hi Peo,
It does not surprise me at all that I would respond to the wrong post
that pretty much sums up my day! I'm sorry, thank you again for you
help, and for clearing up the matter of keeping responses to the forum
I thought you were telling me off for too much chatting for a moment!

Thank you also, David, I'm afraid your solution didn't work for me bu
I do understand your formula better and I don't see why it doesn'
work! Unfortunately it comes up with a function overload error.

Thank you both though!

Karri

 

[Frank Kabel]

Hi David
I think you missed some parenthesis
You probably meant:
=AND(NOT(ISBLANK(E3)),E3=F3)+AND(not(ISBLANK(e11)),E11=F11)+AND(NOT(ISB
LANK(e19)),E19=F19)

--
Regards
Frank Kabel
Frankfurt, Germany

 

[Karrie]

Yes Frank you are right that works also! Thank you! You guys are al
fab!!

 

[David McRitchie]

Hi Frank,
Thanks, I tested it in Excel, but I guess I forgot to copy the correction
back to Excel before posting.

Hi Karrie,
After Frank corrected the parens, my formula is actually the same
as Peo's just written differently.

As far as not hitting the correct posting in a thread -- that is because
you are using ExcelForum and not posting directly to the newsgroups.
Excel Forum appears to simply attaches the reply
to the last posting in the thread. Makes it confusing for everyone.

If you want to see what this thread looks like: (takes 12 hours to be archived)
http://google.com/[email protected]

To see a thread and a subthread not involving ExcelForum
http://google.com/groups?selm=#7pM#[email protected]

Newsreaders would show this same hierarchy of replies to thread, reply,
and properly show subthreads. I don't know for sure but I get the
impression that ExcelForum would just show everything as a reply to the
original posting in a thread.