Expert: Nathan Head - 10/29/2009

Question
I am using Excel 2007. I am trying to create a SUMIF with multiple if statement variables based on the data below. If the conditions of all of the IF statements are true I would like it to add the information and place in a specific cell. Below is the example. This essentially is creating an income statement and returning the actuals for a given date range.

Sheet 1:
Column A - Amount
Column B - Date (mm/dd/yyyy)
Column C - Type of Transaction (Check, Debit...)
Column D - Reference Number (Check Number)
Column E - Budget Header (Operating Expense) Drop Down List
Column F - Budget Line (Electricity) Drop Down List
Column G - Description Free Form Type
Column H - Cleared Yes/No

User enters period number and based on a vlookup on Sheet 2 it returns the month and year (November 2009), start date of the month (11/01/2009) and end date (11/30/2009).

I need to create a formula where the sum of transaction amounts between the above 2 dates (11/01/2009 and 11/30/2009) is returned for a given budget line (Electricity.

If there are 5 transactions each 100 dollars and 3 are in November and 2 are not and and 2 of the 3 in November have electricity the actuals for Electricity should return 200 on sheet 3.

Currently I am able to do an if and formula but it will not allow me to do it for a long list of transactions such as 100 different ones. It does work for an individual cell reference.

Please advise on how this can be done or if more information is needed. Thanks.

Answer
Gareth:

What you need is the SUMPRODUCT function.  The following formula is not exactly what you need, but I believe it will be close (adjust as needed for your actual data):

Lets assume 11/01/2009 is located in a cell called "DATE1" and 11/30/2009 is located in a cell called "DATE2".  Lets assume the cell containing Electricity is called "BLINE".

=SUMPRODUCT((B1:B100>=DATE1)*(B1:B100<=DATE2)*(F1:F100=BLINE)*(A1:A100))

Notes:
1) It is very important all of your ranges are the same size.  Each of the ranges above are exactly 100 rows.  The ranges can only be one column wide.
2) You can just about add in any number of true/false statements to the above formula.  If all statements are TRUE then the amount in column A gets added to the SUMPRODUCT.  If anything is FALSE, then it gets ignored.

Please let me know if you have any additional questions!