SUMPRODUCT or COUNTIF?

[Amy via OfficeKB.com]

I need to get a few counts of data I have entered in several columns filled
with dates from 2004 through 2006 (and will be soon be populated with more
dates from 2006). My goal is to get accurate counts of the dates (by month),
so I can populate a chart. The problem I am running into, is that if I use
the formula below (where Column J has corresponding numbers of the months):

=SUMPRODUCT(--(ISNUMBER(Sheet1!D628)),--(MONTH(Sheet1!D628)=J6))

It's picking up the data for all years - this is a problem.

For January, I want to count all dates in Jan of 2006 and any dates prior to
Jan of 2006. For Feb, I want to count all dates in Feb 2006 only, and then
the same for March through Dec of 2006.

I guess I need two different formulas: (1) One to count all dates in Jan of
2006 and before; and (2) One to count all dates in each month for 2006.

I've been searching the discussions in this section, but have not found
anything that might help me.

Any assistance is GREATLY appreciated!

 

[Guest]

Not tried but won't the following do?
=SUMPRODUCT(--(ISNUMBER(Sheet1!D628)),--(MONTH(Sheet1!D628)=J6),--(YEAR(Sheet1!D628)=2006))

 

[Dave Peterson]

I think I'd add a category:

Before 2006:
=SUMPRODUCT(--(ISNUMBER(Sheet1!D628)),--(year(Sheet1!D628)<2006))

Then use this for all the other months:

=SUMPRODUCT(--(ISNUMBER(Sheet1!D628)),--(MONTH(Sheet1!D628)=J6),
--year(sheet1!d6:d28=2006))

Or you could just use a giant formula in the January cell that adds both.

 

[Amy via OfficeKB.com]

That worked! Thank you so much, both Gary and Dave. I combined both of your
suggestions and it was successful.

Not tried but won't the following do?
=SUMPRODUCT(--(ISNUMBER(Sheet1!D628)),--(MONTH(Sheet1!D628)=J6),--(YEAR(Sheet1!D628)=2006))

I need to get a few counts of data I have entered in several columns filled
with dates from 2004 through 2006 (and will be soon be populated with more

[quoted text clipped - 17 lines]

Any assistance is GREATLY appreciated!

--
Thank you,
Amy@H-D

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-functions/200601/1