Sum Product to Count Dates

[Dathan]

I've adapted a formula I found in the archives, but I need help adapting it. I need it to count instead of sum. Here is the formula:

=SUMPRODUCT(--(YEAR(A5:A500)=YEAR(TODAY())),B5:B500)

It sums each value in column B when the date in column A matches with the current year.

I need to count the number items it is summing. Can anyone help? Thanks!

 

[Datsmart]

Change your formula like this:

=SUMPRODUCT(--(YEAR(A5:A500)=YEAR(TODAY())),--(B5:B500<>""))

 

[Scott Huish]

Take off the sum range:

=SUMPRODUCT(--(YEAR(A5:A500)=YEAR(TODAY())))

 

[Datsmart]

Yah... Like HotPepper said.

 

[Dathan]

Thanks all!!

HotPepper, you get the award for the most direct solution.

That said, I'm going with a derivation of Datsmart's solution, i.e.:

=SUMPRODUCT(--(YEAR(A5:A500)=YEAR(TODAY())),--((B5:B500)>0))

I didn't mention this before, but the dates are already filled in through 2010, so I needed something conditional on whether or not column B contained something. Datsmarts solution accounted for this.