Re: Countif by Year

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Change it to

=SUMPRODUCT((YEAR(A2:A86)=1996)*(AE2:AO86))



--
Regards,

Peo Sjoblom

(No private emails please)


"heater" <heater@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:01F8BDCC-60EC-446D-82A0-1F05D3DBBC54@xxxxxxxxxxxxxxxx
I need a similar formula as =SUMPRODUCT(--(YEAR(A2:A100)=1996),B2:B100), but
If A2:A100 = 1996, then it will count the total in cells AE2:AO86. I
substituted AE2:AO86 where B2:B100 is, but I get #VALUE!.

"Peo Sjoblom" wrote:

One way, assume dates in A2:A100 and the values you want to total in
B2:B100, adapt to fit your own ranges, if you meant you ant the total first
for 1996, then a total for 1997 and so on

=SUMPRODUCT(--(YEAR(A2:A100)=1996),B2:B100)

if you want all years from 1996 to 2005

=SUMPRODUCT(--(YEAR(A2:A100)>=1996),B2:B100)

it will only work if the dates are real dates and thus numerical

--
Regards,

Peo Sjoblom

(No private emails please)


"heater" <heater@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:A2A20BB3-01D1-45C5-904F-75E90EA18E44@xxxxxxxxxxxxxxxx
>I need to count a cell if another cell is in a particular year. For
>example:
> Column S has dates in this format (May-05). The dates range from > Jan-96
> to
> Aug-05. The numbers to be added are in column W. So, I need a formula
> that
> will calcuate a total for 1996, 1997, 1998, through 2005.
>
>



.

Quantcast