?
Solved

excel summing columns where with certain criteria

Posted on 2014-04-02
4
Medium Priority
?
263 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 1000 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 35

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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

593 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