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

Posted on 2009-12-29
Medium Priority
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!

  • 2
  • 2
LVL 50

Expert Comment

by:barry houdini
ID: 26137576
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

ID: 26137641
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

barry houdini earned 1000 total points
ID: 26137674
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

ID: 26138004
thanks for the tips Barry!

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
I came across an unsolved Outlook issue and here is my solution.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

850 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