Excel 'If Then' or 'When' statement

Posted on 2004-11-09
Last Modified: 2008-03-06
OK, I'm doing some accounting in an excel spreadsheet where I list out all of my transactions. I then want to sum up the transactions depending on how they are labeled without sorting them or anything like that. Basically I'm wanting to come up with a formula to look at an entire column and determine if a number should be counted or not. Here's an example:

Col. A       Col. B
deposit     50.00
credit       100.00
credit       120.00
misc.        50.00
deposit     200.00
credit       25.00

Then in the totals section I'm wanting to see this:

deposits    250.00
credits      245.00
misc.        50.00

So for the deposits total I'm wanting to add up all of column b if column a says deposit, and the same for credits and misc. Is this possible? I've thought about making hidden columns where the totals show up if column a says a specific thing and then adding up that hidden column but then every time I come up with a new type for column a i'm going to have to create a new hidden column rather than just copy my formula over again.
Question by:inetomaha
    LVL 16

    Accepted Solution

    You can use the SUMIF( ) function to sum on a criterion:

    Say the transaction type is in A1:A100 and the amount in B1:B100
    -In D1:D3, write the categories exactly the way they are entered in A1:A100 (no trailing 's'):
    -In E1, enter the formula: =SUMIF($A$1:$A$100,E1,$B$1:$B$100)
    - copy E1 (menu Edit > Copy), select E2:E3, and paste (menu Edit > Paste)


    Author Comment

    That worked great!!! Thanks Sebastien!

    LVL 16

    Expert Comment

    Thanks for the points and grade, Marshall.  Glad i could help.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Most of my previous articles (, Rob the ComputorTutor)  on Office looked at the 2003 version, which was virtually identical to the 2002 and 2000 version, and very similar to their 97 and 95 versions.  However, recently, Microsoft int…
    This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
    The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now