How to use if statement

[whiteswan]

Hi,

I have an account excel sheet that keep track of my cheque's/lodgement
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 fo
weeks so the above do not affect my current balance until they ar
cashed. If a cheque is cashed i update my CASHED COLUMN to YES. So
tend to view the ones not cashed as pending and i want to be able t
view my current balance and my balance when all pending cheques ar
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 YE
and NO seperately. Can you use an if else statement to do this or i
there another way.

Cheers
Im Baffle

 

[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,"YES",C:C)
=SUMIF(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.

 

[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