Help with Formula, returns #NAME!

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

bp - 26 Jul 2007 02:50 GMT

Hello,
I am trying to get this formula to work.
=SUM(IF(D5,VALUE<"4/1/07", E5,+ SUM(IF(F5,VALUE<"4/1/07",G5))))

Thanks
bp

Reply to this Message

JMay - 26 Jul 2007 03:24 GMT

Try:

=SUM(IF(D5<DATEVALUE("4/1/07"),E5)+SUM(IF(F5<DATEVALUE("4/1/07"),G5)))

Jim May

> Hello,
> I am trying to get this formula to work.
> =SUM(IF(D5,VALUE<"4/1/07", E5,+ SUM(IF(F5,VALUE<"4/1/07",G5))))
>
> Thanks
> bp

Reply to this Message

bp - 26 Jul 2007 12:13 GMT

Hello Jim,
Thanks, it worked well.
Could you adjust the following formula to accomodate a DATE RANGE.

example does not work:
=SUM(IF(F5>DATEVALUE("3/31/07") AND (F5<DATEVALUE("7/1/07"),E5)

Thanks,
I have worked on these formulas for hours. Searched thru google, microsoft,
and many of the sites you kind folks host, but could not work this out. If
you know of a site that would have shown me the way thru this, please supply
the url. I would enjoy reading up on this topic.
Thank you,
Bill

> Try:
>
[quoted text clipped - 8 lines]
>> Thanks
>> bp

Reply to this Message

JMay - 26 Jul 2007 13:38 GMT

Get DateValue's right first;
then include them inside the AND as: AND(Firstone, Secondone)
Then Insure your If Statement is right: If(TheAndSatement,IFTRUE,IFFALSE)
Finally Wrap it all in the Sum() As SUM(Line3 above)
It's sorta like Building-Blocks (as a kid) - Solve the lowest Values first,
then
the next Up Values Second, etc... all the way up to the top,,,, SUM it all !!

Like so.........

=SUM(IF(AND(F5>DATEVALUE("3/31/2007"),F5<DATEVALUE("7/1/2007")),E5))

The best formula site on the net is Chip Pearson's siite at:

http://www.cpearson.com/excel/topic

Good luck,

Jim May

> Hello Jim,
> Thanks, it worked well.
[quoted text clipped - 23 lines]
> >> Thanks
> >> bp

Reply to this Message

bp - 27 Jul 2007 03:34 GMT

Good Evening Jim,
I really didn't need luck, <bg> (but thank you) the way you explained the
process was very understandable. I couldn't have missed the mark. I have
completed my spreadsheet to track occupation tax. I could not have gained
this type of training in a book, no matter who the author was. I had an
indication of what I wanted to do, but the "how to" was lacking. Thanks for
all of your direction and effective training.
I have not visited the cpearson website yet, but will spend some quality
time there this weekend.
Thank you very much.
Best regards,
Bill

> Get DateValue's right first;
> then include them inside the AND as: AND(Firstone, Secondone)
[quoted text clipped - 48 lines]
>> >> Thanks
>> >> bp

Reply to this Message