• Status: Solved
• Priority: Medium
• Security: Public
• Views: 268

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
0
mdg1
1 Solution

You could do it by manually entering each Childcare instance: =sum(G2,G13)
0

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

Finance AnalystCommented:

This will summarise and group as required.

See attached.

Thanks
Rob H
Copy-of-ee-totalling-per-week-nu.xlsx
0

Author 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.