Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Aug 2009
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  2. #2
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    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)
    Andrew

  3. #3
    Lounger
    Join Date
    Aug 2009
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Have a Look at attached Example Workbook

    Or Use a Pivot table (Might be even better)

    [attachment=88530:SumIfExample.jpg]

    [attachment=88531:SumIfExample.xls]

    Updated with New Version ...
    Attached Images Attached Images
    Attached Files Attached Files
    Andrew

  6. #6
    Lounger
    Join Date
    Aug 2009
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •