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 |
Find dates in a range; then sum values in that range by a criteria
=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 |
Find dates in a range; then sum values in that range by a criteria
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 |
Find dates in a range; then sum values in that range by a crit
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 |
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 |
All times are GMT +1. The time now is 08:28 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com