|
» Using multiple criteria in SUMIF Function
CATEGORY: Summing |
VERSIONS: All Microsoft Excel Versions |
|
Problem: Summing the total from cells in one column that meet criteria based on a range of dates in another column. Solution 1: Use of SUMIF Function (with either DATE or MONTH) or SUMPRODUCT Function (with manually inserted date criteria or cell references to desired dates). By using the SUMPRODUCT Function, you can either include the desired dates to test for in the formula: =SUMPRODUCT((A1:A100>=DATE(2004,9,1)),(A1:A100<=DATE(2004,9,30)),B1:B100) Or you can have helper cells where you can enter the desired start and end dates to use: =SUMPRODUCT((A1:A100>=C1),(A1:A100<=D1),B1:B100) Where cell C1 contains start date, and D1 contains end date. Solution 2: Another option is to using DATE Function along with SUMIF Function to calculate all items up to the desired end date and then subtract all items prior to the desired start date, with the result being the total of items within desired range: =SUMIF(B4:B10,"<"&DATE(2004,10,1),C4:C10)-SUMIF(B4:B10,"<"&DATE(2004,9,1),C4:C10) Solution 3: Use a helper column inserted next to the date column to calculate the MONTH value of the list of dates, this value is then used in a SUMIF formula: =SUMIF(A4:A10,A1,C4:C10) |
| Screenshot // Using multiple criteria in SUMIF Function |
  |
|
RATING:
3.11
VIEWS: 91339 |
||||
Sponsors:
 |
Book Store:
Recommended Books:
- Quantitative Methods in Derivatives Pricing: An Introduction to Computational Finance
- Analyzing Markets, Products, and Marketing Plans
- Seven Habits Of Highly Effective People
- The McGraw-Hill 36-Hour Course in Finance for Nonfinancial Managers
- The New Financial Order: Risk in the Twenty-First Century
- The McGraw-Hill Guide to Writing a High-Impact Business Plan: A Proven Blueprint for First-Time Entrepreneurs
Related MS EXCEL TIPS:
Terms
and Conditions of use
The applications/code on this site are distributed as is and without warranties
or liability. In no event shall the owner of the copyrights, or the authors
of the applications/code be liable for any loss of profit, any problems
or any damage resulting from the use or evaluation of the applications/code.
