excel summing columns where with certain criteria

I have an excel spreadsheet that reflects expenses over a three year period - each row is another specific expense.  For each expense, i have added a column with the week number that the date falls in ( a little problem may be that since there is more than one year, we have the same week number for different years - ie 1/1/2012 is week 1 and 1/1/2013 is week 1).

See attached spreadsheet for an example

So, i want to to create a summing formula so that I can add up all expenses of a particular expense (in this case "child care expense") that fall for each week number and to place the sum in the last row of each week
ee-totalling-per-week-number.xlsx
mdg1Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
nutschConnect With a Mentor Commented:
You can use the below formula in cell I2 and copy it down:

=IF(OR(WEEKNUM(B3)>WEEKNUM(B2),B3=""),SUMIFS(G$1:G2,H$1:H2,"Child care",B$1:B2,">"&B2-WEEKDAY(B2)+1),"")

It checks if the next date is either empty or in a following week, then sums the childcare for the previous week.

EDIT: adjusted so week can stop on any day.

Thomas
0
 
Tim PhillipsWindows Systems AdministratorCommented:
You could do it by manually entering each Childcare instance: =sum(G2,G13)
0
 
Rob HensonFinance AnalystCommented:
How about using Pivot Table?

This will summarise and group as required.

See attached.

Thanks
Rob H
Copy-of-ee-totalling-per-week-nu.xlsx
0
 
mdg1Author Commented:
Thanks - that worked!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.