SUMPRODUCT...SUMIF...Pivot Table????

[Tom K]

HELP!!!! I am racking my brain and Google is not helping.

I track my checkbook online. My goal is to Find out how much I spent on Day Care each year, 01/01/05-12/31/05 and 01/01/06-12/31/06 and 01/01/07-12/31/07.

I would also like to do other categories like Xcel Energy, Minnegasco, Walmart, etc... (I will copy the formula and change the name criteria if needed for them).

I think I should be able to get this in a pivot table (See at bottom) but I am having zero luck. I can get them all listed by individual day in the pivot table, but I can not get a summary for 2005, 2006 or 2007 by Place.

I gave up on the Pivot table and decided to try to do a formula instead. Below is the formula I am trying to do (FYI..cell "I1" has my start date for the formula [01/01/2007] and cell "J1" has the end date [01/01/2008].

=SUM(IF(($A$2:$A$10000>=$I$1),($A$2:$A$10000<=$J$1)*AND($B$2:$B$10000="Day Care"),$C$2:$C$10000))


This formula gives me the sum of all my entries, not just Day Care and not just within the dates I specified.

Below is a sample of the first few lines of my checkbook.

Column A (dt ck is wrote) Column B (Place wrote to) Column C (amt of ck)

DATE PLACE AMOUNT
10/31/05 Pizza Hut $61.41
11/02/05 Falafel King $(4.07)
11/04/05 Steve & Barry's $(19.96)
11/06/05 Falafel King $(4.07)
11/07/05 Wanderer $(28.00)
11/08/05 Athens $(14.89)
11/09/05 Deposit $200.00
11/13/05 Athens $(7.44)
11/13/05 W/D $(20.00)
11/13/05 Rainbow $(60.95)
11/14/05 Target $(7.17)
11/14/05 Park Nicollet $(15.00)


Here is my Pivot Table (As you can see it list out every date. I want it to sum the years).

Sum of AMOUNT PLACE
DATE Day Care Walmart Grand Total
12/24/2005 -57.75 -57.75
1/9/2006 -84.06 -84.06
2/14/2006 -8.31 -8.31
3/11/2006 -56.1 -56.1
5/1/2006 -15.1 -15.1
5/3/2006 -17.88 -17.88
5/11/2006 -25.06 -25.06
5/12/2006 -9.8 -9.8
5/14/2006 -142.05 -142.05
5/16/2006 -7.32 -7.32
5/19/2006 -97.92 -97.92
5/20/2006 -28.98 -28.98
5/31/2006 -54.18 -54.18
6/5/2006 -4.97 -4.97
6/6/2006 -52.25 -52.25
6/13/2006 0 0
6/15/2006 -85.36 -85.36
6/17/2006 -39.05 -39.05
6/21/2006 -58.85 -58.85
6/23/2006 -28.96 -28.96
6/26/2006 -110.83 -110.83
7/3/2006 -21.84 -21.84
7/4/2006 -64.81 -64.81
Grand Total -2889 -5433.98 -8322.98

[jasoncw]

Hi, Tom.

For the formula, try:

Please Login or Register  to view this content.

HTH

Jason

[hemanu]

The Pivot table is the best option

1) Before you create a pivot table
2) create a column and call it year
3) use the Year function in excel to strip the year from date of your expenses. this will allow to see the expenses by year
4) Create your pivot table. Make sure that you include the year column
5) Expense type should be in the row section
6) The year column should be added to the column section

[Tom K]

Jason- Thanks that worked for the account I was working on.... Followup. How do I write this formula If I track 3 accounts (set up exactly the same) on 3 different spreadsheets in the same workbook. I tried
=SUMPRODUCT(('ABC CHECKING:[XYZ BANK]XYZ BANK'!$B$2:$B$10000>=$I$1)*('ABC CHECKING:[XYZ BANK]XYZ BANK'!$B$2:$B$10000<=$J$1)*('ABC CHECKING:[XYZ BANK]XYZ BANK'!$C$2:$C$10000="Day Care")*('ABC CHECKING:[XYZ BANK]XYZ BANK'!$D$2:$D$10000)) and I got a #REF! error.


hemanu- Thanks for your help as well. I got the pivot table to work. I inserted an extra column at the begining and typed in this formula to get the year =TEXT(B2,"yy"). Same as above... How do I use the data from all 3 worksheets? I tried to use data from all 3 sheets but when I get to the end and click finish it Says Pivot table can not be opened. I tried the 1st option Microsoft list and the 3rd option Multiple consolidation ranges, neither work.

[no dice]

Hey Tom - I'm in the pivot table camp on this one. Multiple consolidation ranges will work if the sheets have identical column structure, but you lose some flexibility with the pivot table itself.

Can you combine your 3 sheets into 1? That would give you the most flexibility and the greatest capability to slice & dice.

BTW - you could also get year by "=YEAR(B2)"

[drichards68]

very helpful jasoncw, thank you.