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

=SUMPRODUCT(--(A2:A366=DATE(2005,1,1)),--(A2:A366<=DATE(2005,1,31)),--(B2:B
366="Rent"),C2:C366)

in Swedish

=PRODUKTSUMMA(--(A2:A366=DATUM(2005;1;1));--(A2:A366<=DATUM(2005;1;31));--(
B2:B366="Hyra");C2:C366)

will do it for January 2005


--

Regards,

Peo Sjoblom



"Anders" <andersUNDERSCOREsjomanATyahooDOTcom wrote in message
...
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

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

Hej Peo,

Stort tack för hjälpen. Det löste problemet snyggt o elegant.

Mvh
Anders




"Peo Sjoblom" wrote:

=SUMPRODUCT(--(A2:A366=DATE(2005,1,1)),--(A2:A366<=DATE(2005,1,31)),--(B2:B
366="Rent"),C2:C366)

in Swedish

=PRODUKTSUMMA(--(A2:A366=DATUM(2005;1;1));--(A2:A366<=DATUM(2005;1;31));--(
B2:B366="Hyra");C2:C366)

will do it for January 2005


--

Regards,

Peo Sjoblom



"Anders" <andersUNDERSCOREsjomanATyahooDOTcom wrote in message
...
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

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