How to use if statement

[whiteswan]

Hi,

I have an account excel sheet that keep track of my cheque's/lodgements etc

I have 4 columns DATE,CHEQUE NO,AMOUNT, CASHED here is a quick example

15/12/2005 224 200.00 YES
20/12/2005 225 100.00 YES
01/01/2006 226 400.00 NO
02/01/2006 227 500.00 NO

these are cheques i made out to people but they may not cash them for weeks so the above do not affect my current balance until they are cashed. If a cheque is cashed i update my CASHED COLUMN to YES. So i tend to view the ones not cashed as pending and i want to be able to view my current balance and my balance when all pending cheques are cashed.

in another worksheet it shows my balances:

current balance 2000.00
current pending 900.00
theo balance 1100.00

Now what i want it to be able to do is calculate the fields marked YES and NO seperately. Can you use an if else statement to do this or is there another way.

Cheers
Im Baffled

[Dav]

IF amount iin column C and Cashed is in ColumnD something along the lines of

=Sumif(d2:d100,"Yes",c2:c100) Cashed
=Sumif(d2:d100,"No",c2:c100) Pending
=sum(c2:c100) Total

Regards

Dav

[whiteswan]

Thanks Dav

[Arvi Laanemets]

Hi

=SUMIF(D:D,"YES",C:C)
=SUMIF(D:D,"NO",C:C)

But why have those summary values on separate sheet - add a couple empty
rows at top of sheet, enter formulas for all 3 summary values there (be
careful that no numbers or formulas returning numeric value are entered into
column C), and use Freeze Panes to keep those top rows along with column
headers always visible.


--
Arvi Laanemets
( My real mail address: arvi.laanemets<at>tarkon.ee )



"whiteswan" <[email protected]> wrote
in message news:[email protected]...
>
> Hi,
>
> I have an account excel sheet that keep track of my cheque's/lodgements
> etc
>
> I have 4 columns DATE,CHEQUE NO,AMOUNT, CASHED here is a quick example
>
> 15/12/2005 224 200.00 YES
> 20/12/2005 225 100.00 YES
> 01/01/2006 226 400.00 NO
> 02/01/2006 227 500.00 NO
>
> these are cheques i made out to people but they may not cash them for
> weeks so the above do not affect my current balance until they are
> cashed. If a cheque is cashed i update my CASHED COLUMN to YES. So i
> tend to view the ones not cashed as pending and i want to be able to
> view my current balance and my balance when all pending cheques are
> cashed.
>
> in another worksheet it shows my balances:
>
> current balance 2000.00
> current pending 900.00
> theo balance 1100.00
>
> Now what i want it to be able to do is calculate the fields marked YES
> and NO seperately. Can you use an if else statement to do this or is
> there another way.
>
> Cheers
> Im Baffled
>
>
> --
> whiteswan
> ------------------------------------------------------------------------
> whiteswan's Profile:
> http://www.excelforum.com/member.php...o&userid=31995
> View this thread: http://www.excelforum.com/showthread...hreadid=517265
>

[whiteswan]

Even better, good idea. BTW what are freeze panes can you give me an example??

[Dav]

If you go to a given cell in your worksheet and choose windows/freeze panes all the cells to the left and above the cell you are in are frozen, so if your balance was in this area you could always see it, no matter where you were on the spreadsheet. the rest of the sheet still moves

Regards

Dav