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.

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.

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.