Excel Formula's? - Totally Lost!!

[Tican]

hi all,

I am trying to create a spreadsheet for help with tracking Petty Cash at work, I know an IF statement is required, but have no idea what is needed

At work we only have Excel 2003, so do not have as many options as I do at home (2007)

So in the missing column I need it to automatically add up all of the NO Columns, without having to manually do the sum (=SUM(B3:B4) etc.) every time. Is this possible?

 

[Lee]

It could be done using a pivot table.

 

[Tican]

It could be done using a pivot table.

Ideally I would need it on that sheet though

I was trying out SUMIF but got no where

=SUMIF(B:B,D="NO",)

got stuck after no

 

[crazyDAJT]

simple and clear way to do it.

don't use yes and no in the received column, put the actual value received. then just sum up the column and to get outstanding subtracting that form the required total.

if you want to allow quick reference to see what is ok and what is outstanding. create another column. and use

IF=Amount OK
IF<Amount OUTSTANDING
IF>Amount OVERPAID

or something like that, don't have a copy of Excel to hand so statements wont be quite correct.

although not as concise as doing it in one go, i think its a netter and much simpler solution. I will work out the singing and dancing version if you want when i get to work tomorrow.

 

[Diddyman]

Based on the sample above and if I understand right all you need somewhere is:

=SUM IF(D2: D5,"No",B2:B5)

Diddy

PS No Space between "D2:" and "D5" was getting : D =

 

[Tican]

Based on the sample above and if I understand right all you need somewhere is:

=SUM IF(D2: D5,"No",B2:B5)

Diddy

PS No Space between "D2:" and "D5" was getting : D =

OMG, that works a treat,

Thank you, thank you, thank you