Adding cells based on dates

Excel Excel 2007
April 6, 2010 at 19:05:23
Specs: Windows XP
I am looking to create a macro that when run goes through a sheet and adds all cells that are in the same row as a certain date and places them in a cell on another sheet. I want to macro to cycle through all the days in a month. I am doing this to calculate daily expenses and income. I am figuring that I will have to use a loop and calculate the number of rows that have data. Use this number to cycle through every row in a column for a certain date, and use an if statement to add data out of the other column to a variable and at the end of that date write variable to cell. Change data and clear variable and start the loop over again. Just not sure how to write this. Any help would be appreciated

See More: Adding cells based on dates

Report •


#1
April 6, 2010 at 20:16:54
Please explain your task in a little more detail.

Perhaps an example of your sheet layout would help.

I'm not convinced you need a macro, but even if you do, you haven't given us enough info to work with.

If you use the pre tags above the comments box you can line up your data like this to make it easier to read:

      A        B     C     D    E
1  2/3/2010  Lunch  $10   Pay  $20
2  2/4/2010  Lunch  $ 7   Pay  $20
3  etc.


Report •

#2
April 6, 2010 at 20:33:43
This is the type of setup I am using for my spreadsheet. I need to be able to have flexibility on the number of entries per day. I need to be able to add all totals of expenses and put them in sheet2 as well as income totals on sheet 2

sheet 1
A B C D
1 Date Category Amount Income Amount
2 2/3/2010 Lunch $10
3 2/3/2010 Diner $ 7
4 2/4/2010 Lunch $10
5 2/4/2010 Diner $ 7
6 2/5/2010 Pay $100


Sheet2
A B C
1 Date Total expense Total income
2 2/3/2010 $17
3 2/4/2010 $17
4 2/5/2010 $100


Report •

#3
April 7, 2010 at 04:08:37
I noticed that you didn't use the pre tags as suggested. It really does help line up the data and make it easier for us to understand your layout.

Assuming you have this...

    A           B       C        D
1 Date      Category Amount Income Amount
2 2/3/2010   Lunch    $10
3 2/3/2010   Diner    $ 7
4 2/4/2010   Lunch    $10
5 2/4/2010   Diner    $ 7
6 2/5/2010   Pay               $100


1 - Make a list of your days in A2:A6 of Sheet2.
2 - In B2 place this formula and drag it down:

=SUMIF(Sheet1!$A$2:$A$6,A2,Sheet1!$C$2:$C$6)

3 - In C2, place this formula and drag it down:

=SUMIF(Sheet1!$A$2:$A$6,A2,Sheet1!$D$2:$D$6)



Report •

Related Solutions

#4
April 7, 2010 at 17:56:18
Thanks. This was exactly what I was looking for.

Report •

#5
April 7, 2010 at 20:32:41
Glad I could help.

Report •

Ask Question