Countif by Year

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.

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" <[email protected]> wrote in message
news:[email protected]...
>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.
>
>

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" <[email protected]> wrote in message
> news:[email protected]...
> >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.
> >
> >
>
>

Change it to

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



--
Regards,

Peo Sjoblom

(No private emails please)


"heater" <[email protected]> wrote in message
news:[email protected]...
>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" <[email protected]> wrote in message
>> news:[email protected]...
>> >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.
>> >
>> >
>>
>>

I still get #VALUE!. FYI: The results in AE2:AO86 are "1's" in each cell,
so I was trying to count how many "1's" were in 1996. However, I guess I
could just look in column "T" for how many times 1996 occurs. Would it be
possible to just look in column "T" for the year 1996. Remember, my format
in column "T" is Jan-96.
I tried =COUNTIF(T2:T86,"96"), but the result was 0.

"Peo Sjoblom" wrote:

> Change it to
>
> =SUMPRODUCT((YEAR(A2:A86)=1996)*(AE2:AO86))
>
>
>
> --
> Regards,
>
> Peo Sjoblom
>
> (No private emails please)
>
>
> "heater" <[email protected]> wrote in message
> news:[email protected]...
> >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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> >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.
> >> >
> >> >
> >>
> >>
>
>

Do you have any #value! errors in any of those cells that are referred to in
your formula?

Are you positive that the values in T2:T86 are dates just formatted as Jan-96.
Or are those values just plain old text?

If you try:
=isnumber(t2)
do you get true (indicating that that one is a date) or do you get False
(indicating that that one is just plain old text).

If you have just plain old text, you could use:

=sumproduct(--(right(t2:t86,2)="96"))



heater wrote:
>
> I still get #VALUE!. FYI: The results in AE2:AO86 are "1's" in each cell,
> so I was trying to count how many "1's" were in 1996. However, I guess I
> could just look in column "T" for how many times 1996 occurs. Would it be
> possible to just look in column "T" for the year 1996. Remember, my format
> in column "T" is Jan-96.
> I tried =COUNTIF(T2:T86,"96"), but the result was 0.
>
> "Peo Sjoblom" wrote:
>
> > Change it to
> >
> > =SUMPRODUCT((YEAR(A2:A86)=1996)*(AE2:AO86))
> >
> >
> >
> > --
> > Regards,
> >
> > Peo Sjoblom
> >
> > (No private emails please)
> >
> >
> > "heater" <[email protected]> wrote in message
> > news:[email protected]...
> > >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" <[email protected]> wrote in message
> > >> news:[email protected]...
> > >> >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.
> > >> >
> > >> >
> > >>
> > >>
> >
> >

--

Dave Peterson