Find dates in a range; then sum values in that range by a criteria

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
news:736FA450-DFB0-4C93-8949-10B42770CADD@microsoft.com...
> 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
>
>

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
> news:736FA450-DFB0-4C93-8949-10B42770CADD@microsoft.com...
> > 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

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
>