Solved

excel summing columns where with certain criteria

Posted on 2014-04-02
4
251 Views
Last Modified: 2014-04-07
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
Comment
Question by:mdg1
4 Comments
 
LVL 6

Expert Comment

by:Tim Phillips
ID: 39973761
You could do it by manually entering each Childcare instance: =sum(G2,G13)
0
 
LVL 39

Accepted Solution

by:
nutsch earned 250 total points
ID: 39973768
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
 
LVL 32

Expert Comment

by:Rob Henson
ID: 39974536
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
 

Author Closing Comment

by:mdg1
ID: 39983755
Thanks - that worked!
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question