SUMIF Formula (I think...)
 

Hi,

I have a workbook (well obviously or I wouldn't be here...), Excel 2003.
One sheet is a running sheet containing a summary of invoices for the month -
one invoice per row. A10 contains the date of the invoice, G10 contains a
code, eg 3, which equals 21days. What I want to do is this: IF G10=3, then
A10 + 21, or IF G10 = 4, then A10 + 30(days) or if G10 = 5 then add 30 days
from End Of Month (have no idea how to display this). I want Column H to
display the due date of payment, and be highligted if past due.

Is this possible? Have been playing around with some formulas, but so far
no joy.

--
Thanks for the help

SUMIF Formula (I think...)
 

in H10,

=IF(G10=3,A10+21,IF(G10=4,A10+30,IF(G10=5,EOMONTH( A10,0)+30,"")))

Then select column H and go to Format|Conditional Formatting...

Set parameters:

Cell Value Greater Than =Today()

Click Format and choose from Pattern tab.


--
NBVC

Where there is a will there are many ways.

'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=127948

SUMIF Formula (I think...)
 

Hi NBVC,

Thank you so much for your formula, it works perfectly for 3 & 4, however 5
doesn't work - it's returning the #NAME? error.
--
Thanks for the help


"NBVC" wrote:


in H10,

=IF(G10=3,A10+21,IF(G10=4,A10+30,IF(G10=5,EOMONTH( A10,0)+30,"")))

Then select column H and go to Format|Conditional Formatting...

Set parameters:

Cell Value Greater Than =Today()

Click Format and choose from Pattern tab.


--
NBVC

Where there is a will there are many ways.

'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=127948

SUMIF Formula (I think...)
 

JS;462696 Wrote:
Hi NBVC,

Thank you so much for your formula, it works perfectly for 3 & 4,
however 5
doesn't work - it's returning the #NAME? error.
--
Thanks for the help


"NBVC" wrote:


in H10,

=IF(G10=3,A10+21,IF(G10=4,A10+30,IF(G10=5,EOMONTH( A10,0)+30,"")))

Then select column H and go to Format|Conditional Formatting...

Set parameters:

Cell Value Greater Than =Today()

Click Format and choose from Pattern tab.


--
NBVC

Where there is a will there are many ways.

'The Code Cage' ('The Code Cage - Microsoft Office Help - Microsoft
Office Discussion' (http://www.thecodecage.com))

------------------------------------------------------------------------
NBVC's Profile: 'The Code Cage Forums - View Profile: NBVC'
(http://www.thecodecage.com/forumz/members/nbvc.html)
View this thread: 'SUMIF Formula (I think...) - The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh...d.php?t=127948)




EOMONTH() is an analysis toolpak addin function...

Go to Tools|Addins and check the Analysis Toolpak addin.. click Ok...to
install


--
NBVC

Where there is a will there are many ways.

'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=127948

SUMIF Formula (I think...)
 

You can also use: DATE(YEAR(A10),MONTH(A10)+1,0)
to replace: EOMONTH(A10,0)
in NBVC's expression

btw, do hit the YES's (like the one below) in all responses which help
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"JS" wrote:
.. however 5 doesn't work - it's returning the #NAME? error

SUMIF Formula (I think...)
 

Max;462716 Wrote:
You can also use: DATE(YEAR(A10),MONTH(A10)+1,0)
to replace: EOMONTH(A10,0)
in NBVC's expression

btw, do hit the YES's (like the one below) in all responses which help
--
Max
Singapore
'Savefile - Free File Upload' (http://savefile.com/projects/236895)
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"JS" wrote:
.. however 5 doesn't work - it's returning the #NAME? error

I did mention that ;)


--
NBVC

Where there is a will there are many ways.

'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=127948

SUMIF Formula (I think...)
 

Hi Max,

Thank you for your response. The formula works, however it's returning the
incorrect date. If the date in column A is say 12/07/09, it returns 31/07/09
when it needs to return 31/08/09. Sorry if I hadn't made that clear.
--
Thanks for the help


"Max" wrote:

You can also use: DATE(YEAR(A10),MONTH(A10)+1,0)
to replace: EOMONTH(A10,0)
in NBVC's expression

btw, do hit the YES's (like the one below) in all responses which help
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"JS" wrote:
.. however 5 doesn't work - it's returning the #NAME? error

SUMIF Formula (I think...)
 

Just easily adjust the MONTH part of it to suit, viz.:
DATE(YEAR(A10),MONTH(A10)+2,0)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"JS" wrote:
Hi Max,

Thank you for your response. The formula works, however it's returning the
incorrect date. If the date in column A is say 12/07/09, it returns 31/07/09
when it needs to return 31/08/09. Sorry if I hadn't made that clear.

SUMIF Formula (I think...)
 

Perfect Max!
--
Thanks for the help!


"Max" wrote:

Just easily adjust the MONTH part of it to suit, viz.:
DATE(YEAR(A10),MONTH(A10)+2,0)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"JS" wrote:
Hi Max,

Thank you for your response. The formula works, however it's returning the
incorrect date. If the date in column A is say 12/07/09, it returns 31/07/09
when it needs to return 31/08/09. Sorry if I hadn't made that clear.

SUMIF Formula (I think...)
 

Welcome, glad to hear
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"JS" wrote in message
...
Perfect Max!
--
Thanks for the help!