Excel: Calculate the total amount if certain criteria is met?

Posted on 2009-12-29
Last Modified: 2012-05-08
Hi All Excel experts!

I would need to create a formula that is counting the total number of completed and cancelled tasks from different worksheets

I have attached an example (I have marked the amounts in red which I would need to have automatically calculated)

I.e  If the status is 'Completed' and the task name is 'Test1' --> calculate the total amount of these (from all workbooks)

thanks in advance!

    LVL 50

    Expert Comment

    by:barry houdini
    How many worksheets will there be? You could use a formula which adds the total for each worksheet....but that might not be practical if you have a large number of worksheets....i.e. for C9 in your example
    =SUMPRODUCT(('1 week'!A17:A24=B9)*('1 week'!B17:B24="Completed"))+SUMPRODUCT(('2 week'!A17:A24=B9)*('2 week'!B17:B24="Completed"))
    which version of Excel are you using?
    regards, barry

    Author Comment

    I was actually planning to have several weeks so it means I would have at least 20 worksheets.
    I tried the tip you gave and it worked! :) Do you see issues coming if I used it with several worksheets (the formula would become pretty long, not sure if there's a max length it can have?)
    I'm using 2003
    LVL 50

    Accepted Solution

    An alternative would be to use a SUMPRODUCT formula on each sheet to give the total completed for Test1 etc. (in the same cell on each worksheet) then use a formula on the summary sheet to add the same cell from multiple sheets, e.g. if you have worksheets 1 week to 20 week then you can sum D4 on each of those sheets with this formula
    =SUM('1 week:20 week'!D4)
    regards, barry

    Author Comment

    thanks for the tips Barry!

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Many companies are making the switch from Microsoft to Google Apps ( Use this article to learn more about what Google Apps has to offer and to help if you’re planning on migrating to Google Apps. It is …
    Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
    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: …
    This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

    733 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

    23 Experts available now in Live!

    Get 1:1 Help Now