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!D6:D28)),--(MONTH(Sheet1!D6:D28)=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!

--
Thank you,
Amy@H-D

Message posted via http://www.officekb.com

[Gary L Brown]

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

--
Gary Brown
gary_brown@ge_NOSPAM.com
If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"Amy via OfficeKB.com" wrote:

> 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!D6:D28)),--(MONTH(Sheet1!D6:D28)=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!
>
> --
> Thank you,
> Amy@H-D
>
> Message posted via http://www.officekb.com
>

[Dave Peterson]

I think I'd add a category:

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

Then use this for all the other months:

=SUMPRODUCT(--(ISNUMBER(Sheet1!D6:D28)),--(MONTH(Sheet1!D6:D28)=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" wrote:
>
> 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!D6:D28)),--(MONTH(Sheet1!D6:D28)=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!
>
> --
> Thank you,
> Amy@H-D
>
> Message posted via http://www.officekb.com

--

Dave Peterson

[Amy via OfficeKB.com]

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



Gary L Brown wrote:
>Not tried but won't the following do?
>=SUMPRODUCT(--(ISNUMBER(Sheet1!D6:D28)),--(MONTH(Sheet1!D6:D28)=J6),--(YEAR(Sheet1!D6:D28)=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.a...tions/200601/1