Find dates in a range; then sum values in that range by a crit
Hi Dave,
Many thanks for the suggestion, which very elegantly solved my problem.
Anders
"Dave Peterson" wrote:
Something like this might work:
=sumproduct(--(a1:a10<=date(2005,12,31)),--(a1:a10=date(2005,1,1)),
--(b1:b10="Rent"),c1:c10)
This will check to see if the date is in 2005, and has Rent in the associated
row, and add the values in C1:C10.
You can make the ranges as large as you want, but you can't use the whole
column--and all the ranges have to be the same size.
=sumproduct(--(a1:a10<=f1),--(a1:a10=f1)),--(b1:b10=g1),c1:c10)
(If you put the dates in F1 and the category in G1.)
Anders wrote:
Hi,
For my private finances, I am trying to group all my monthly expenses. I
have downloaded from my bank all expenses for the last year, and marked each
entry with the type of expense (rent, electricity, food, fun, etc.) This
leaves me with a table with three columns: Date, Amount and Type
Now, in a separate part, I would like to create a formula that first finds
dates within a range (I give start and end dates), and that then would sum up
the values for a given criteria (for instance rent). This would allow me to
see, e.g. how much I spent on food between such and such a date.
I have been mocking around with IF, SUM, SUMIF statements, but cannot get it
to work. I have managed the second part (take a criteria, such as rent, and
sum all values in a given range) using SUMIF, but cannot figure out how I can
input a start and end value to define the range, instead of hardcoding the
range with cell references.
Anybody got any thoughts?
Many thanks,
Anders Sjöman, Stockholm
--
Dave Peterson
|