Solved

excel summing columns where with certain criteria

Posted on 2014-04-02
4
252 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 33

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

821 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