Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
counting cells associated by year and date.
I have a large table that has a numerical count of observations for every day
of the year for 5 years. however i am trying to sum that data by month for each year in a differenet table. The data is in this following form: Year Month Day Amendment Count 2003 1 1 65596 2003 1 2 64991 2003 1 3 85596 2003 1 4 81660 2003 1 5 76551 2003 1 6 80182 2003 1 7 85333 I have been trying to make it work with Vlookup but that just returns a single number and not the sum for that month and particular year as i was trying to achieve. Is it possible? Thanks in advance. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
counting cells associated by year and date.
For example, to get January 2003:
=SUMPRODUCT((A1:A1000 = 2003)*(B1:B1000 = 1)*C1:C1000) But better would be to create real dates: =DATE(A2,B2,C2) copied down, and use those dates and the count in a Pivot table, then easily group by year, month, etc. HTH, Bernie MS Excel MVP "mebsmith" wrote in message ... I have a large table that has a numerical count of observations for every day of the year for 5 years. however i am trying to sum that data by month for each year in a differenet table. The data is in this following form: Year Month Day Amendment Count 2003 1 1 65596 2003 1 2 64991 2003 1 3 85596 2003 1 4 81660 2003 1 5 76551 2003 1 6 80182 2003 1 7 85333 I have been trying to make it work with Vlookup but that just returns a single number and not the sum for that month and particular year as i was trying to achieve. Is it possible? Thanks in advance. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
counting cells associated by year and date.
thanks!
"Bernie Deitrick" wrote: For example, to get January 2003: =SUMPRODUCT((A1:A1000 = 2003)*(B1:B1000 = 1)*C1:C1000) But better would be to create real dates: =DATE(A2,B2,C2) copied down, and use those dates and the count in a Pivot table, then easily group by year, month, etc. HTH, Bernie MS Excel MVP "mebsmith" wrote in message ... I have a large table that has a numerical count of observations for every day of the year for 5 years. however i am trying to sum that data by month for each year in a differenet table. The data is in this following form: Year Month Day Amendment Count 2003 1 1 65596 2003 1 2 64991 2003 1 3 85596 2003 1 4 81660 2003 1 5 76551 2003 1 6 80182 2003 1 7 85333 I have been trying to make it work with Vlookup but that just returns a single number and not the sum for that month and particular year as i was trying to achieve. Is it possible? Thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Obtain a Year to Date result without all cells of data being fille | Excel Worksheet Functions | |||
Due Date with year using IF for several cells | Excel Worksheet Functions | |||
remove a year from date value in mulitple cells | Excel Worksheet Functions | |||
counting date entries by month & year | Excel Worksheet Functions | |||
Seed date formats to different year in different cells | Excel Discussion (Misc queries) |