Calculating expenses from checkbook type data

  • Thread starter Thread starter Brian
  • Start date Start date
B

Brian

Hello,

I have data in the form of:
DATE AMOUNT

like:
June 1, 2002 $100
June 5, 2002 $100
July 1, 2003 $125
Aug 9, 2003 $150

The data comes from an external program, and I would prefer to leave it
in this format so I can easily update it from the program.

I have had no luck so far in figuring out a function that will sum up
all the values in AMOUNT where the year from the DATE column is 2002 or
2003. The goal is to tell how much was spent each year.

Does anyone have an idea on how to do this?

Thank You.
-Brian

PS: I'm also shooting for per month per year, but that's just a bonus
right now.
 
I would insert two new columns, one for month and one for
year. To get the data, the formula would be =TEXT
(A2,"MMMM") and =TEXT(A2,"YYYY"). These would return June
and 2002 respectively. You can then do a SUMIF on those
new columns, such as =SUMIF(C2:C200,"=2002",B2:B200) where
the column B is AMOUNT and C is YEAR. You can always hide
your new Month and Year columns if you don't want to
print/view them.

As an added tip, the part of the SUMIF formula above which
says "=2002" can be a reference to another cell. This
could be useful for copying the formula instead of having
to change the reference in each cell. If 2002 appears in
cell A202, the formula would be =SUMIF
(C2:C200,"="&A202,B2:B200). When copying the formula
across or down, make sure your anchors ($) are in place.
Also, be careful with the year. The text formula above
makes 2002 a text cell, even though it looks like a
number. Any reference functions like sumif or vlookup
will perceive the cell as text and will return an error
message if the lookup value is not text as well. To fix
the error in this case, either make the text formula =TEXT
(A2,"YYYY")*1 or put '2002 in cell A202.

Hope I didn't confuse you too much.
Jane
jane.graves @ infores.com
 
Back
Top