Gareth
Member
Registered: 2nd Mar 00
Location: Derby, Drives: EVO VIII MR & pug 308
User status: Offline
|
Not for me for my miss's i will let her type it, ta.
I want cell S2 to add up cells N2:Q2 only if cell M2 is blank. When I type a date in M2 I want S2 to be 0. It's an accounting spreadsheet and I need an overdue column. If there's an easier way of doing this then please tell me!
[Edited on 23-07-2011 by Gareth]
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Online
|
=IF(M2<>"",0,SUM(N2:Q2))
|
Gareth
Member
Registered: 2nd Mar 00
Location: Derby, Drives: EVO VIII MR & pug 308
User status: Offline
|
Works a treat! Many thanks for the speedy reply, really appreciate it! :-)
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Online
|
|
Brett
Premium Member
Registered: 16th Dec 02
Location: Manchester
User status: Offline
|
Skills
|
Gareth
Member
Registered: 2nd Mar 00
Location: Derby, Drives: EVO VIII MR & pug 308
User status: Offline
|
Sorry to be a pain but missed a piece of the puzzle, I also need it to do if L2 is passed todays date and M2 is blank, add up the other columns. Is this possible? Thanks.
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Online
|
Can't test it here, try this.
=IF(AND(M2<>"",L2>TODAY()),0,SUM(N2:Q2))
|
Gareth
Member
Registered: 2nd Mar 00
Location: Derby, Drives: EVO VIII MR & pug 308
User status: Offline
|
Thanks, but it still displays the figure in S2, whether M2 is blank or not.
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Online
|
=IF(AND(M2="",L2>TODAY()),0,SUM(N2:Q2))
Not sure and no way to test here, heading home in a minute I'll have a proper read of what you need.
|
Gareth
Member
Registered: 2nd Mar 00
Location: Derby, Drives: EVO VIII MR & pug 308
User status: Offline
|
Tried the change but still the same. I'm not very good with this :-) Instead would be better is if L2 is passed the date and M2 is empty, display what is in J2 in S2. Thats the one, if possible. Really appreciate your help, if you can send me your e-mail address I will paypal you some money across for your time. Thanks again.
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Online
|
In S2, put
=IF(AND(L2 > TODAY(),M2 = ""),J2,0)
If that doesn't work email me some rows from the sheet to ian@corsasport.co.uk
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Online
|
Done that the sheet I got, they're all zero in those five rows, is that correct?
Ideally need a few different rows with different outcomes, one where it's not paid, one where it's not due and not paid, one where it's overdue etc.
If the formula doesn't concern the earlier columns you can get rid of those as well to email it.
|
