SUMIF Formula (I think...)

J

JS

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.
 
N

NBVC

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

--
NBV

Where there is a will there are many ways.

'The Code Cage' (http://www.thecodecage.com
 
J

JS

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.
 
N

NBVC

JS;462696 said:
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



Office Discussion' (http://www.thecodecage.com))

------------------------------------------------------------------------
Click to expand...


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

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

--
NBV

Where there is a will there are many ways.

'The Code Cage' (http://www.thecodecage.com
 
M

Max

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
 
J

JS

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to change date format to 7 working days a week in ms project 2010? 0
calculating payment terms "End of Month +30". 1
Avoiding Leads, using SVT, and Linking Tasks correctly 0
Look Up Formula With Veried Search Range 2
How can I copy data produced by a formula, not the formula? 2
Help with formula 3
Use TODAY() function as the criteria in a SUMIF formula 1
How do I limit formula results to positive only? 4

Top