How to write this formula please?

Topic

New Reply

Hi

Can someone please tell me (I’m working in Excel 2000) how to write the formula that will only add a cell if another cell on the same row has certain data in it. To be more specific, we have a spreadsheet with all vehicle expenditure on it but we want to be able to have a front summary sheet detailing the individual vehicles expenditure (that is linked to the long list) rather than having to use the drop down list.

Thanks in advance.

Penny

Reply | Quote

Replies

Sounds like you need to use an IF Function or maybe a SUMIF
Can you give an actual example?

The General Principle of ADDING a Value in a Cell with an IF is

=IF(CellToCheck=””,0,CellToADD)

So in a Formula that might appear as

=C1+IF(B1=””,0,D1)

Reply | Quote

Hi Andrew!

Thanks for your reply (fairly local, all things considered! I’m near Stamford in Lincs!)……

In column ‘E’ are the amounts spent. In column ‘F’ are the vehicle reg numbers. So I need to extrapolate the all the amounts in Column ‘E’ that are in the same row as a certain reg number that is held in Column ‘F’. see below……

Col E Col F

14.00 KU52 PTT
14.00 PT04 HHH
14.00 TP04 SJH
14.00 X886 JJR
14.00 JT02 SJH
19.50 PT57 JHT
149.00 JT58 SJP

Hope this helps? Thanks again!

Penny

Reply | Quote

Penny,

This seems like a perfect application for the SubTotals function.
If you can sort the data on the registration numbers then goto Data->Subtotals…
In the dialog box select “Registration NO” in the “At each change in” field then
check what ever heading you have in Col E in the “Add subtotal to:” field and click OK.

Hope this helps.

RG

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Have a Look at attached Example Workbook

Or Use a Pivot table (Might be even better)

Updated with New Version …

Reply | Quote

Thanks for your reply, RG

Andrew, thank you for your time in sending the spreadsheet…it’s worked a treat so that’s great!

Thanks once again!

Penny

Reply | Quote