Countif by Year

[Peo Sjoblom]

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@discussions.microsoft.com> wrote in message
news:A2A20BB3-01D1-45C5-904F-75E90EA18E44@microsoft.com...
>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.
>
>

[heater]

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.

[heater]

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@discussions.microsoft.com> wrote in message
> news:A2A20BB3-01D1-45C5-904F-75E90EA18E44@microsoft.com...
> >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.
> >
> >
>
>

[Peo Sjoblom]

Change it to

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



--
Regards,

Peo Sjoblom

(No private emails please)


"heater" <heater@discussions.microsoft.com> wrote in message
news:01F8BDCC-60EC-446D-82A0-1F05D3DBBC54@microsoft.com...
>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@discussions.microsoft.com> wrote in message
>> news:A2A20BB3-01D1-45C5-904F-75E90EA18E44@microsoft.com...
>> >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.
>> >
>> >
>>
>>

[heater]

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" <heater@discussions.microsoft.com> wrote in message
> news:01F8BDCC-60EC-446D-82A0-1F05D3DBBC54@microsoft.com...
> >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@discussions.microsoft.com> wrote in message
> >> news:A2A20BB3-01D1-45C5-904F-75E90EA18E44@microsoft.com...
> >> >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]

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" <heater@discussions.microsoft.com> wrote in message
> > news:01F8BDCC-60EC-446D-82A0-1F05D3DBBC54@microsoft.com...
> > >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@discussions.microsoft.com> wrote in message
> > >> news:A2A20BB3-01D1-45C5-904F-75E90EA18E44@microsoft.com...
> > >> >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

[Dave Peterson]

Ahhh. I didn't notice that you used AE2:AO86.

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

You'd have to use a single column (AE2:AE86 or AO2:Ao86) for this formula to
work.


heater wrote:
>
> I did not find any errors. A box popped up that said "No cells were found".
> I copied your formula, but still get #VALUE!. In AE2:AO86 there are just
> numbers. I even highlighted AE2:AO86 and formatted to numbers to be sure.
>
> "Dave Peterson" wrote:
>
> > Select those columns
> > Edit|goto|special
> > Click Constants
> > Uncheck: Numbers, text, logicals (keep Errors checked)
> >
> > Did you find any errors?
> >
> > Then do the same for formulas:
> > Edit|goto|special
> > Click Formulas
> > Uncheck: Numbers, text, logicals (keep Errors checked)
> >
> > Did you find any errors?
> >
> > ====
> > If you're using this formula:
> > =SUMPRODUCT((YEAR(A2:A86)=1996)*(AE2:AO86))
> >
> > Make sure AE2:Ao86 contains nothing but numbers--or empty cells. Any text in
> > that range could be the problem, too.
> >
> > heater wrote:
> > >
> > > I get a TRUE answer. They are all formatted as Jan-96. The dates range from
> > > 1996 to 2005.
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > 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" <heater@discussions.microsoft.com> wrote in message
> > > > > > news:01F8BDCC-60EC-446D-82A0-1F05D3DBBC54@microsoft.com...
> > > > > > >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@discussions.microsoft.com> wrote in message
> > > > > > >> news:A2A20BB3-01D1-45C5-904F-75E90EA18E44@microsoft.com...
> > > > > > >> >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
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

[heater]

I did not find any errors. A box popped up that said "No cells were found".
I copied your formula, but still get #VALUE!. In AE2:AO86 there are just
numbers. I even highlighted AE2:AO86 and formatted to numbers to be sure.

"Dave Peterson" wrote:

> Select those columns
> Edit|goto|special
> Click Constants
> Uncheck: Numbers, text, logicals (keep Errors checked)
>
> Did you find any errors?
>
> Then do the same for formulas:
> Edit|goto|special
> Click Formulas
> Uncheck: Numbers, text, logicals (keep Errors checked)
>
> Did you find any errors?
>
> ====
> If you're using this formula:
> =SUMPRODUCT((YEAR(A2:A86)=1996)*(AE2:AO86))
>
> Make sure AE2:Ao86 contains nothing but numbers--or empty cells. Any text in
> that range could be the problem, too.
>
> heater wrote:
> >
> > I get a TRUE answer. They are all formatted as Jan-96. The dates range from
> > 1996 to 2005.
> >
> > "Dave Peterson" wrote:
> >
> > > 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" <heater@discussions.microsoft.com> wrote in message
> > > > > news:01F8BDCC-60EC-446D-82A0-1F05D3DBBC54@microsoft.com...
> > > > > >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@discussions.microsoft.com> wrote in message
> > > > > >> news:A2A20BB3-01D1-45C5-904F-75E90EA18E44@microsoft.com...
> > > > > >> >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
> > >
>
> --
>
> Dave Peterson
>

[Dave Peterson]

Select those columns
Edit|goto|special
Click Constants
Uncheck: Numbers, text, logicals (keep Errors checked)

Did you find any errors?

Then do the same for formulas:
Edit|goto|special
Click Formulas
Uncheck: Numbers, text, logicals (keep Errors checked)

Did you find any errors?

====
If you're using this formula:
=SUMPRODUCT((YEAR(A2:A86)=1996)*(AE2:AO86))

Make sure AE2:Ao86 contains nothing but numbers--or empty cells. Any text in
that range could be the problem, too.

heater wrote:
>
> I get a TRUE answer. They are all formatted as Jan-96. The dates range from
> 1996 to 2005.
>
> "Dave Peterson" wrote:
>
> > 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" <heater@discussions.microsoft.com> wrote in message
> > > > news:01F8BDCC-60EC-446D-82A0-1F05D3DBBC54@microsoft.com...
> > > > >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@discussions.microsoft.com> wrote in message
> > > > >> news:A2A20BB3-01D1-45C5-904F-75E90EA18E44@microsoft.com...
> > > > >> >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
> >

--

Dave Peterson

[heater]

I get a TRUE answer. They are all formatted as Jan-96. The dates range from
1996 to 2005.

"Dave Peterson" wrote:

> 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" <heater@discussions.microsoft.com> wrote in message
> > > news:01F8BDCC-60EC-446D-82A0-1F05D3DBBC54@microsoft.com...
> > > >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@discussions.microsoft.com> wrote in message
> > > >> news:A2A20BB3-01D1-45C5-904F-75E90EA18E44@microsoft.com...
> > > >> >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
>

[heater]

So, what you are saying is there is not a formula to count AE2:AO86. IS
there a way to just look in column "T" and count how many times 1996, 1997,
1998, and so on to 2005 occurs?

"Dave Peterson" wrote:

> Ahhh. I didn't notice that you used AE2:AO86.
>
> =SUMPRODUCT((YEAR(A2:A86)=1996)*(AE2:AO86))
>
> You'd have to use a single column (AE2:AE86 or AO2:Ao86) for this formula to
> work.
>
>
> heater wrote:
> >
> > I did not find any errors. A box popped up that said "No cells were found".
> > I copied your formula, but still get #VALUE!. In AE2:AO86 there are just
> > numbers. I even highlighted AE2:AO86 and formatted to numbers to be sure.
> >
> > "Dave Peterson" wrote:
> >
> > > Select those columns
> > > Edit|goto|special
> > > Click Constants
> > > Uncheck: Numbers, text, logicals (keep Errors checked)
> > >
> > > Did you find any errors?
> > >
> > > Then do the same for formulas:
> > > Edit|goto|special
> > > Click Formulas
> > > Uncheck: Numbers, text, logicals (keep Errors checked)
> > >
> > > Did you find any errors?
> > >
> > > ====
> > > If you're using this formula:
> > > =SUMPRODUCT((YEAR(A2:A86)=1996)*(AE2:AO86))
> > >
> > > Make sure AE2:Ao86 contains nothing but numbers--or empty cells. Any text in
> > > that range could be the problem, too.
> > >
> > > heater wrote:
> > > >
> > > > I get a TRUE answer. They are all formatted as Jan-96. The dates range from
> > > > 1996 to 2005.
> > > >
> > > > "Dave Peterson" wrote:
> > > >
> > > > > 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" <heater@discussions.microsoft.com> wrote in message
> > > > > > > news:01F8BDCC-60EC-446D-82A0-1F05D3DBBC54@microsoft.com...
> > > > > > > >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@discussions.microsoft.com> wrote in message
> > > > > > > >> news:A2A20BB3-01D1-45C5-904F-75E90EA18E44@microsoft.com...
> > > > > > > >> >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
> > > > >
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

[Dave Peterson]

=sumproduct(--(year(t2:t86)=96))

Should work--if they're really dates and no errors <bg>.

heater wrote:
>
> So, what you are saying is there is not a formula to count AE2:AO86. IS
> there a way to just look in column "T" and count how many times 1996, 1997,
> 1998, and so on to 2005 occurs?
>
> "Dave Peterson" wrote:
>
> > Ahhh. I didn't notice that you used AE2:AO86.
> >
> > =SUMPRODUCT((YEAR(A2:A86)=1996)*(AE2:AO86))
> >
> > You'd have to use a single column (AE2:AE86 or AO2:Ao86) for this formula to
> > work.
> >
> >
> > heater wrote:
> > >
> > > I did not find any errors. A box popped up that said "No cells were found".
> > > I copied your formula, but still get #VALUE!. In AE2:AO86 there are just
> > > numbers. I even highlighted AE2:AO86 and formatted to numbers to be sure.
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > Select those columns
> > > > Edit|goto|special
> > > > Click Constants
> > > > Uncheck: Numbers, text, logicals (keep Errors checked)
> > > >
> > > > Did you find any errors?
> > > >
> > > > Then do the same for formulas:
> > > > Edit|goto|special
> > > > Click Formulas
> > > > Uncheck: Numbers, text, logicals (keep Errors checked)
> > > >
> > > > Did you find any errors?
> > > >
> > > > ====
> > > > If you're using this formula:
> > > > =SUMPRODUCT((YEAR(A2:A86)=1996)*(AE2:AO86))
> > > >
> > > > Make sure AE2:Ao86 contains nothing but numbers--or empty cells. Any text in
> > > > that range could be the problem, too.
> > > >
> > > > heater wrote:
> > > > >
> > > > > I get a TRUE answer. They are all formatted as Jan-96. The dates range from
> > > > > 1996 to 2005.
> > > > >
> > > > > "Dave Peterson" wrote:
> > > > >
> > > > > > 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" <heater@discussions.microsoft.com> wrote in message
> > > > > > > > news:01F8BDCC-60EC-446D-82A0-1F05D3DBBC54@microsoft.com...
> > > > > > > > >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@discussions.microsoft.com> wrote in message
> > > > > > > > >> news:A2A20BB3-01D1-45C5-904F-75E90EA18E44@microsoft.com...
> > > > > > > > >> >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
> > > > > >
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

[heater]

I formatted column "T" to dates, to make sure again, but no luck. The below
fomula at least gives me a "0", I guess I will just have to count manually.
I give up.


"Dave Peterson" wrote:

> =sumproduct(--(year(t2:t86)=96))
>
> Should work--if they're really dates and no errors <bg>.
>
> heater wrote:
> >
> > So, what you are saying is there is not a formula to count AE2:AO86. IS
> > there a way to just look in column "T" and count how many times 1996, 1997,
> > 1998, and so on to 2005 occurs?
> >
> > "Dave Peterson" wrote:
> >
> > > Ahhh. I didn't notice that you used AE2:AO86.
> > >
> > > =SUMPRODUCT((YEAR(A2:A86)=1996)*(AE2:AO86))
> > >
> > > You'd have to use a single column (AE2:AE86 or AO2:Ao86) for this formula to
> > > work.
> > >
> > >
> > > heater wrote:
> > > >
> > > > I did not find any errors. A box popped up that said "No cells were found".
> > > > I copied your formula, but still get #VALUE!. In AE2:AO86 there are just
> > > > numbers. I even highlighted AE2:AO86 and formatted to numbers to be sure.
> > > >
> > > > "Dave Peterson" wrote:
> > > >
> > > > > Select those columns
> > > > > Edit|goto|special
> > > > > Click Constants
> > > > > Uncheck: Numbers, text, logicals (keep Errors checked)
> > > > >
> > > > > Did you find any errors?
> > > > >
> > > > > Then do the same for formulas:
> > > > > Edit|goto|special
> > > > > Click Formulas
> > > > > Uncheck: Numbers, text, logicals (keep Errors checked)
> > > > >
> > > > > Did you find any errors?
> > > > >
> > > > > ====
> > > > > If you're using this formula:
> > > > > =SUMPRODUCT((YEAR(A2:A86)=1996)*(AE2:AO86))
> > > > >
> > > > > Make sure AE2:Ao86 contains nothing but numbers--or empty cells. Any text in
> > > > > that range could be the problem, too.
> > > > >
> > > > > heater wrote:
> > > > > >
> > > > > > I get a TRUE answer. They are all formatted as Jan-96. The dates range from
> > > > > > 1996 to 2005.
> > > > > >
> > > > > > "Dave Peterson" wrote:
> > > > > >
> > > > > > > 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" <heater@discussions.microsoft.com> wrote in message
> > > > > > > > > news:01F8BDCC-60EC-446D-82A0-1F05D3DBBC54@microsoft.com...
> > > > > > > > > >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@discussions.microsoft.com> wrote in message
> > > > > > > > > >> news:A2A20BB3-01D1-45C5-904F-75E90EA18E44@microsoft.com...
> > > > > > > > > >> >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
> > > > > > >
> > > > >
> > > > > --
> > > > >
> > > > > Dave Peterson
> > > > >
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

[Dave Peterson]

Formatting the column as dates won't change the value in the cell--it'll only
change the display.

If you format that column to General, do all the cells change to a number?

If not, you still have text in that range.

heater wrote:
>
> I formatted column "T" to dates, to make sure again, but no luck. The below
> fomula at least gives me a "0", I guess I will just have to count manually.
> I give up.
>
>
> "Dave Peterson" wrote:
>
> > =sumproduct(--(year(t2:t86)=96))
> >
> > Should work--if they're really dates and no errors <bg>.
> >
> > heater wrote:
> > >
> > > So, what you are saying is there is not a formula to count AE2:AO86. IS
> > > there a way to just look in column "T" and count how many times 1996, 1997,
> > > 1998, and so on to 2005 occurs?
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > Ahhh. I didn't notice that you used AE2:AO86.
> > > >
> > > > =SUMPRODUCT((YEAR(A2:A86)=1996)*(AE2:AO86))
> > > >
> > > > You'd have to use a single column (AE2:AE86 or AO2:Ao86) for this formula to
> > > > work.
> > > >
> > > >
> > > > heater wrote:
> > > > >
> > > > > I did not find any errors. A box popped up that said "No cells were found".
> > > > > I copied your formula, but still get #VALUE!. In AE2:AO86 there are just
> > > > > numbers. I even highlighted AE2:AO86 and formatted to numbers to be sure.
> > > > >
> > > > > "Dave Peterson" wrote:
> > > > >
> > > > > > Select those columns
> > > > > > Edit|goto|special
> > > > > > Click Constants
> > > > > > Uncheck: Numbers, text, logicals (keep Errors checked)
> > > > > >
> > > > > > Did you find any errors?
> > > > > >
> > > > > > Then do the same for formulas:
> > > > > > Edit|goto|special
> > > > > > Click Formulas
> > > > > > Uncheck: Numbers, text, logicals (keep Errors checked)
> > > > > >
> > > > > > Did you find any errors?
> > > > > >
> > > > > > ====
> > > > > > If you're using this formula:
> > > > > > =SUMPRODUCT((YEAR(A2:A86)=1996)*(AE2:AO86))
> > > > > >
> > > > > > Make sure AE2:Ao86 contains nothing but numbers--or empty cells. Any text in
> > > > > > that range could be the problem, too.
> > > > > >
> > > > > > heater wrote:
> > > > > > >
> > > > > > > I get a TRUE answer. They are all formatted as Jan-96. The dates range from
> > > > > > > 1996 to 2005.
> > > > > > >
> > > > > > > "Dave Peterson" wrote:
> > > > > > >
> > > > > > > > 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" <heater@discussions.microsoft.com> wrote in message
> > > > > > > > > > news:01F8BDCC-60EC-446D-82A0-1F05D3DBBC54@microsoft.com...
> > > > > > > > > > >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@discussions.microsoft.com> wrote in message
> > > > > > > > > > >> news:A2A20BB3-01D1-45C5-904F-75E90EA18E44@microsoft.com...
> > > > > > > > > > >> >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
> > > > > > > >
> > > > > >
> > > > > > --
> > > > > >
> > > > > > Dave Peterson
> > > > > >
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

[heater]

Yes - When I format to General, they all change to a number.

"Dave Peterson" wrote:

> Formatting the column as dates won't change the value in the cell--it'll only
> change the display.
>
> If you format that column to General, do all the cells change to a number?
>
> If not, you still have text in that range.
>
> heater wrote:
> >
> > I formatted column "T" to dates, to make sure again, but no luck. The below
> > fomula at least gives me a "0", I guess I will just have to count manually.
> > I give up.
> >
> >
> > "Dave Peterson" wrote:
> >
> > > =sumproduct(--(year(t2:t86)=96))
> > >
> > > Should work--if they're really dates and no errors <bg>.
> > >
> > > heater wrote:
> > > >
> > > > So, what you are saying is there is not a formula to count AE2:AO86. IS
> > > > there a way to just look in column "T" and count how many times 1996, 1997,
> > > > 1998, and so on to 2005 occurs?
> > > >
> > > > "Dave Peterson" wrote:
> > > >
> > > > > Ahhh. I didn't notice that you used AE2:AO86.
> > > > >
> > > > > =SUMPRODUCT((YEAR(A2:A86)=1996)*(AE2:AO86))
> > > > >
> > > > > You'd have to use a single column (AE2:AE86 or AO2:Ao86) for this formula to
> > > > > work.
> > > > >
> > > > >
> > > > > heater wrote:
> > > > > >
> > > > > > I did not find any errors. A box popped up that said "No cells were found".
> > > > > > I copied your formula, but still get #VALUE!. In AE2:AO86 there are just
> > > > > > numbers. I even highlighted AE2:AO86 and formatted to numbers to be sure.
> > > > > >
> > > > > > "Dave Peterson" wrote:
> > > > > >
> > > > > > > Select those columns
> > > > > > > Edit|goto|special
> > > > > > > Click Constants
> > > > > > > Uncheck: Numbers, text, logicals (keep Errors checked)
> > > > > > >
> > > > > > > Did you find any errors?
> > > > > > >
> > > > > > > Then do the same for formulas:
> > > > > > > Edit|goto|special
> > > > > > > Click Formulas
> > > > > > > Uncheck: Numbers, text, logicals (keep Errors checked)
> > > > > > >
> > > > > > > Did you find any errors?
> > > > > > >
> > > > > > > ====
> > > > > > > If you're using this formula:
> > > > > > > =SUMPRODUCT((YEAR(A2:A86)=1996)*(AE2:AO86))
> > > > > > >
> > > > > > > Make sure AE2:Ao86 contains nothing but numbers--or empty cells. Any text in
> > > > > > > that range could be the problem, too.
> > > > > > >
> > > > > > > heater wrote:
> > > > > > > >
> > > > > > > > I get a TRUE answer. They are all formatted as Jan-96. The dates range from
> > > > > > > > 1996 to 2005.
> > > > > > > >
> > > > > > > > "Dave Peterson" wrote:
> > > > > > > >
> > > > > > > > > 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" <heater@discussions.microsoft.com> wrote in message
> > > > > > > > > > > news:01F8BDCC-60EC-446D-82A0-1F05D3DBBC54@microsoft.com...
> > > > > > > > > > > >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@discussions.microsoft.com> wrote in message
> > > > > > > > > > > >> news:A2A20BB3-01D1-45C5-904F-75E90EA18E44@microsoft.com...
> > > > > > > > > > > >> >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
> > > > > > > > >
> > > > > > >
> > > > > > > --
> > > > > > >
> > > > > > > Dave Peterson
> > > > > > >
> > > > >
> > > > > --
> > > > >
> > > > > Dave Peterson
> > > > >
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

[Dave Peterson]

Are you sure the data is in column T.

Do you have any hidden rows that might be affecting the formula?

(I'm almost out of guesses!)

heater wrote:
>
> Yes - When I format to General, they all change to a number.
>
> "Dave Peterson" wrote:
>
> > Formatting the column as dates won't change the value in the cell--it'll only
> > change the display.
> >
> > If you format that column to General, do all the cells change to a number?
> >
> > If not, you still have text in that range.
> >
> > heater wrote:
> > >
> > > I formatted column "T" to dates, to make sure again, but no luck. The below
> > > fomula at least gives me a "0", I guess I will just have to count manually.
> > > I give up.
> > >
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > =sumproduct(--(year(t2:t86)=96))
> > > >
> > > > Should work--if they're really dates and no errors <bg>.
> > > >
> > > > heater wrote:
> > > > >
> > > > > So, what you are saying is there is not a formula to count AE2:AO86. IS
> > > > > there a way to just look in column "T" and count how many times 1996, 1997,
> > > > > 1998, and so on to 2005 occurs?
> > > > >
> > > > > "Dave Peterson" wrote:
> > > > >
> > > > > > Ahhh. I didn't notice that you used AE2:AO86.
> > > > > >
> > > > > > =SUMPRODUCT((YEAR(A2:A86)=1996)*(AE2:AO86))
> > > > > >
> > > > > > You'd have to use a single column (AE2:AE86 or AO2:Ao86) for this formula to
> > > > > > work.
> > > > > >
> > > > > >
> > > > > > heater wrote:
> > > > > > >
> > > > > > > I did not find any errors. A box popped up that said "No cells were found".
> > > > > > > I copied your formula, but still get #VALUE!. In AE2:AO86 there are just
> > > > > > > numbers. I even highlighted AE2:AO86 and formatted to numbers to be sure.
> > > > > > >
> > > > > > > "Dave Peterson" wrote:
> > > > > > >
> > > > > > > > Select those columns
> > > > > > > > Edit|goto|special
> > > > > > > > Click Constants
> > > > > > > > Uncheck: Numbers, text, logicals (keep Errors checked)
> > > > > > > >
> > > > > > > > Did you find any errors?
> > > > > > > >
> > > > > > > > Then do the same for formulas:
> > > > > > > > Edit|goto|special
> > > > > > > > Click Formulas
> > > > > > > > Uncheck: Numbers, text, logicals (keep Errors checked)
> > > > > > > >
> > > > > > > > Did you find any errors?
> > > > > > > >
> > > > > > > > ====
> > > > > > > > If you're using this formula:
> > > > > > > > =SUMPRODUCT((YEAR(A2:A86)=1996)*(AE2:AO86))
> > > > > > > >
> > > > > > > > Make sure AE2:Ao86 contains nothing but numbers--or empty cells. Any text in
> > > > > > > > that range could be the problem, too.
> > > > > > > >
> > > > > > > > heater wrote:
> > > > > > > > >
> > > > > > > > > I get a TRUE answer. They are all formatted as Jan-96. The dates range from
> > > > > > > > > 1996 to 2005.
> > > > > > > > >
> > > > > > > > > "Dave Peterson" wrote:
> > > > > > > > >
> > > > > > > > > > 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" <heater@discussions.microsoft.com> wrote in message
> > > > > > > > > > > > news:01F8BDCC-60EC-446D-82A0-1F05D3DBBC54@microsoft.com...
> > > > > > > > > > > > >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@discussions.microsoft.com> wrote in message
> > > > > > > > > > > > >> news:A2A20BB3-01D1-45C5-904F-75E90EA18E44@microsoft.com...
> > > > > > > > > > > > >> >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
> > > > > > > > > >
> > > > > > > >
> > > > > > > > --
> > > > > > > >
> > > > > > > > Dave Peterson
> > > > > > > >
> > > > > >
> > > > > > --
> > > > > >
> > > > > > Dave Peterson
> > > > > >
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

[heater]

It works now. For some reason when I was working on the spreadsheet
yesterday it returned a "0" answer. When I opened it up today and changed
the formula to look for 1997, it worked. So, I assume my spreadsheet had
some sort of corruption in it and when it was shut down and reopened, it
cleared it up. Sorry for wasting your time.

This is the formula that worked: =SUMPRODUCT(--(YEAR($T$2:$T$150)=1996))

"Dave Peterson" wrote:

> Are you sure the data is in column T.
>
> Do you have any hidden rows that might be affecting the formula?
>
> (I'm almost out of guesses!)
>
> heater wrote:
> >
> > Yes - When I format to General, they all change to a number.
> >
> > "Dave Peterson" wrote:
> >
> > > Formatting the column as dates won't change the value in the cell--it'll only
> > > change the display.
> > >
> > > If you format that column to General, do all the cells change to a number?
> > >
> > > If not, you still have text in that range.
> > >
> > > heater wrote:
> > > >
> > > > I formatted column "T" to dates, to make sure again, but no luck. The below
> > > > fomula at least gives me a "0", I guess I will just have to count manually.
> > > > I give up.
> > > >
> > > >
> > > > "Dave Peterson" wrote:
> > > >
> > > > > =sumproduct(--(year(t2:t86)=96))
> > > > >
> > > > > Should work--if they're really dates and no errors <bg>.
> > > > >
> > > > > heater wrote:
> > > > > >
> > > > > > So, what you are saying is there is not a formula to count AE2:AO86. IS
> > > > > > there a way to just look in column "T" and count how many times 1996, 1997,
> > > > > > 1998, and so on to 2005 occurs?
> > > > > >
> > > > > > "Dave Peterson" wrote:
> > > > > >
> > > > > > > Ahhh. I didn't notice that you used AE2:AO86.
> > > > > > >
> > > > > > > =SUMPRODUCT((YEAR(A2:A86)=1996)*(AE2:AO86))
> > > > > > >
> > > > > > > You'd have to use a single column (AE2:AE86 or AO2:Ao86) for this formula to
> > > > > > > work.
> > > > > > >
> > > > > > >
> > > > > > > heater wrote:
> > > > > > > >
> > > > > > > > I did not find any errors. A box popped up that said "No cells were found".
> > > > > > > > I copied your formula, but still get #VALUE!. In AE2:AO86 there are just
> > > > > > > > numbers. I even highlighted AE2:AO86 and formatted to numbers to be sure.
> > > > > > > >
> > > > > > > > "Dave Peterson" wrote:
> > > > > > > >
> > > > > > > > > Select those columns
> > > > > > > > > Edit|goto|special
> > > > > > > > > Click Constants
> > > > > > > > > Uncheck: Numbers, text, logicals (keep Errors checked)
> > > > > > > > >
> > > > > > > > > Did you find any errors?
> > > > > > > > >
> > > > > > > > > Then do the same for formulas:
> > > > > > > > > Edit|goto|special
> > > > > > > > > Click Formulas
> > > > > > > > > Uncheck: Numbers, text, logicals (keep Errors checked)
> > > > > > > > >
> > > > > > > > > Did you find any errors?
> > > > > > > > >
> > > > > > > > > ====
> > > > > > > > > If you're using this formula:
> > > > > > > > > =SUMPRODUCT((YEAR(A2:A86)=1996)*(AE2:AO86))
> > > > > > > > >
> > > > > > > > > Make sure AE2:Ao86 contains nothing but numbers--or empty cells. Any text in
> > > > > > > > > that range could be the problem, too.
> > > > > > > > >
> > > > > > > > > heater wrote:
> > > > > > > > > >
> > > > > > > > > > I get a TRUE answer. They are all formatted as Jan-96. The dates range from
> > > > > > > > > > 1996 to 2005.
> > > > > > > > > >
> > > > > > > > > > "Dave Peterson" wrote:
> > > > > > > > > >
> > > > > > > > > > > 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" <heater@discussions.microsoft.com> wrote in message
> > > > > > > > > > > > > news:01F8BDCC-60EC-446D-82A0-1F05D3DBBC54@microsoft.com...
> > > > > > > > > > > > > >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@discussions.microsoft.com> wrote in message
> > > > > > > > > > > > > >> news:A2A20BB3-01D1-45C5-904F-75E90EA18E44@microsoft.com...
> > > > > > > > > > > > > >> >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
> > > > > > > > > > >
> > > > > > > > >
> > > > > > > > > --
> > > > > > > > >
> > > > > > > > > Dave Peterson
> > > > > > > > >
> > > > > > >
> > > > > > > --
> > > > > > >
> > > > > > > Dave Peterson
> > > > > > >
> > > > >
> > > > > --
> > > > >
> > > > > Dave Peterson
> > > > >
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

[Dave Peterson]

Doh!

This formula:
=sumproduct(--(year(t2:t86)=96))
wouldn't work too well.

But
=sumproduct(--(year(t2:t86)=1996))
may work better!

(I screwed it up when I went from text to real dates! Sorry.)


heater wrote:
>
> It works now. For some reason when I was working on the spreadsheet
> yesterday it returned a "0" answer. When I opened it up today and changed
> the formula to look for 1997, it worked. So, I assume my spreadsheet had
> some sort of corruption in it and when it was shut down and reopened, it
> cleared it up. Sorry for wasting your time.
>
> This is the formula that worked: =SUMPRODUCT(--(YEAR($T$2:$T$150)=1996))
>
> "Dave Peterson" wrote:
>
> > Are you sure the data is in column T.
> >
> > Do you have any hidden rows that might be affecting the formula?
> >
> > (I'm almost out of guesses!)
> >
> > heater wrote:
> > >
> > > Yes - When I format to General, they all change to a number.
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > Formatting the column as dates won't change the value in the cell--it'll only
> > > > change the display.
> > > >
> > > > If you format that column to General, do all the cells change to a number?
> > > >
> > > > If not, you still have text in that range.
> > > >
> > > > heater wrote:
> > > > >
> > > > > I formatted column "T" to dates, to make sure again, but no luck. The below
> > > > > fomula at least gives me a "0", I guess I will just have to count manually.
> > > > > I give up.
> > > > >
> > > > >
> > > > > "Dave Peterson" wrote:
> > > > >
> > > > > > =sumproduct(--(year(t2:t86)=96))
> > > > > >
> > > > > > Should work--if they're really dates and no errors <bg>.
> > > > > >
> > > > > > heater wrote:
> > > > > > >
> > > > > > > So, what you are saying is there is not a formula to count AE2:AO86. IS
> > > > > > > there a way to just look in column "T" and count how many times 1996, 1997,
> > > > > > > 1998, and so on to 2005 occurs?
> > > > > > >
> > > > > > > "Dave Peterson" wrote:
> > > > > > >
> > > > > > > > Ahhh. I didn't notice that you used AE2:AO86.
> > > > > > > >
> > > > > > > > =SUMPRODUCT((YEAR(A2:A86)=1996)*(AE2:AO86))
> > > > > > > >
> > > > > > > > You'd have to use a single column (AE2:AE86 or AO2:Ao86) for this formula to
> > > > > > > > work.
> > > > > > > >
> > > > > > > >
> > > > > > > > heater wrote:
> > > > > > > > >
> > > > > > > > > I did not find any errors. A box popped up that said "No cells were found".
> > > > > > > > > I copied your formula, but still get #VALUE!. In AE2:AO86 there are just
> > > > > > > > > numbers. I even highlighted AE2:AO86 and formatted to numbers to be sure.
> > > > > > > > >
> > > > > > > > > "Dave Peterson" wrote:
> > > > > > > > >
> > > > > > > > > > Select those columns
> > > > > > > > > > Edit|goto|special
> > > > > > > > > > Click Constants
> > > > > > > > > > Uncheck: Numbers, text, logicals (keep Errors checked)
> > > > > > > > > >
> > > > > > > > > > Did you find any errors?
> > > > > > > > > >
> > > > > > > > > > Then do the same for formulas:
> > > > > > > > > > Edit|goto|special
> > > > > > > > > > Click Formulas
> > > > > > > > > > Uncheck: Numbers, text, logicals (keep Errors checked)
> > > > > > > > > >
> > > > > > > > > > Did you find any errors?
> > > > > > > > > >
> > > > > > > > > > ====
> > > > > > > > > > If you're using this formula:
> > > > > > > > > > =SUMPRODUCT((YEAR(A2:A86)=1996)*(AE2:AO86))
> > > > > > > > > >
> > > > > > > > > > Make sure AE2:Ao86 contains nothing but numbers--or empty cells. Any text in
> > > > > > > > > > that range could be the problem, too.
> > > > > > > > > >
> > > > > > > > > > heater wrote:
> > > > > > > > > > >
> > > > > > > > > > > I get a TRUE answer. They are all formatted as Jan-96. The dates range from
> > > > > > > > > > > 1996 to 2005.
> > > > > > > > > > >
> > > > > > > > > > > "Dave Peterson" wrote:
> > > > > > > > > > >
> > > > > > > > > > > > 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" <heater@discussions.microsoft.com> wrote in message
> > > > > > > > > > > > > > news:01F8BDCC-60EC-446D-82A0-1F05D3DBBC54@microsoft.com...
> > > > > > > > > > > > > > >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@discussions.microsoft.com> wrote in message
> > > > > > > > > > > > > > >> news:A2A20BB3-01D1-45C5-904F-75E90EA18E44@microsoft.com...
> > > > > > > > > > > > > > >> >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
> > > > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > > --
> > > > > > > > > >
> > > > > > > > > > Dave Peterson
> > > > > > > > > >
> > > > > > > >
> > > > > > > > --
> > > > > > > >
> > > > > > > > Dave Peterson
> > > > > > > >
> > > > > >
> > > > > > --
> > > > > >
> > > > > > Dave Peterson
> > > > > >
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson