Sum between two date ranges, and account number.

[Huey_IP]

I need some help.

I have an Excel workbook in which one spreadsheet "Budget Expenses" contains invoices that were paid and are entered as they are received (in no specific date order). The spreadsheet also contains the amount paid, and the account number it came out of.

For example:
On 9/20/2010 I spent $52.00 from account number 4700
The date is in Cell A1, the amount in in cell E1, and the account it came from is in cell F1.

The other spreadsheet in the workbook “Budget Totals” needs to calculate what I spent each quarter from each account number. It calculates the total spent per quarter from a specified account number.

Now... I need to calculate the amounts spent per quarter. So... I want to add up all the entries that fall within the first quarter date range in A1-A999 from 1 Oct 2010 to 31 December 2010. Then I want the formula to look at column F1-999 to determine if I spent it from account number 4700. Once it meets all that criteria I want it to sum up the totals in cell ranges E1-E999. The outcome of that formula will then be contained in a cell in the “Budget Totals” spreadsheet.

Also, is there any way to indicate infinity for a vertical cell range instead of typing A1-A999?

Thanks for any help you can give,
Steve

 

[Akashwani]

Hi Steve,

If you can add some sample data to your post that may help get you a suitable reply, you also need to state which version of Excel you are using.
I don't believe this is the best solution, but it's all I can think of.

Excel Workbook
	A	B	C	D	E	F	G
1	Date	*	*	*	Amount	Account	Helper
2	01/01/2010	*	*	*	1,000.00	ABC123	Q1
3	20/01/2010	*	*	*	500.00	ABC124	Q1
4	30/01/2010	*	*	*	250.00	ABC125	Q1
5	02/02/2010	*	*	*	100.00	ABC126	Q1
6	25/02/2010	*	*	*	500.00	ABC123	Q1
7	10/03/2010	*	*	*	100.00	ABC124	Q1
8	25/04/2010	*	*	*	250.00	ABC125	Q2
9	30/05/2010	*	*	*	100.00	ABC126	Q2
10	02/06/2010	*	*	*	100.00	ABC123	Q2
11	20/07/2010	*	*	*	125.00	ABC124	Q3
12	21/07/2010	*	*	*	750.00	ABC125	Q3
13	01/08/2010	*	*	*	50.00	ABC126	Q3
14	30/09/2010	*	*	*	100.00	ABC123	Q3
15	01/10/2010	*	*	*	125.00	ABC124	Q4
16	02/10/2010	*	*	*	400.00	ABC125	Q4
17	30/11/2010	*	*	*	125.00	ABC126	Q4
18	01/12/2010	*	*	*	125.00	ABC123	Q4
19	22/12/2010	*	*	*	2,000.00	ABC124	Q4
20	23/12/2010	*	*	*	2,500.00	ABC125	Q4
Budget Expenses

The formula in G2 needs to be copied down.

Excel Workbook
	A	B	C	D	E	F	G	H
1	Quarter	Account	Amount	Amount	*	*	Helper Data
2	Q4	ABC124	2,125.00	2,125.00	*	*	01/01/2010	Q1
3	*	*	*	*	*	*	01/02/2010	Q1
4	*	*	*	*	*	*	01/03/2010	Q1
5	Q1	ABC123	ABC124	ABC125	ABC126	*	01/04/2010	Q2
6	Amount	1,500.00	600.00	250.00	100.00	*	01/05/2010	Q2
7	Amount	1,500.00	600.00	250.00	100.00	*	01/06/2010	Q2
8	*	*	*	*	*	*	01/07/2010	Q3
9	Q2	ABC123	ABC124	ABC125	ABC126	*	01/08/2010	Q3
10	Amount	100.00	0.00	250.00	100.00	*	01/09/2010	Q3
11	Amount	100.00	0.00	250.00	100.00	*	01/10/2010	Q4
12	*	*	*	*	*	*	01/11/2010	Q4
13	Q3	ABC123	ABC124	ABC125	ABC126	*	01/12/2010	Q4
14	Amount	100.00	125.00	750.00	50.00	*	*	*
15	Amount	100.00	125.00	750.00	50.00	*	*	*
16	*	*	*	*	*	*	*	*
17	Q4	ABC123	ABC124	ABC125	ABC126	*	*	*
18	Amount	125.00	2,125.00	2,900.00	125.00	*	*	*
19	Amount	125.00	2,125.00	2,900.00	125.00	*	*	*
Budget Totals

The formulas in B6:B19 need to be copied across.
As you can see I have given you 2 formula options.
Sumifs will only work in 2007-10
Sumproduct will work in all versions.

In A2:B2 you can type in the criteria for 1 Quarter and 1 Account if you wish.

To answer your last question try A:A instead of A1:A999

I hope this helps or gives you some ideas, if not it has bumped you up for someone else to look at.

Good luck.

Ak