+ Reply to Thread
Results 1 to 18 of 18

Countif by Year

  1. 08-31-2005, 01:05 PM #1
    Peo Sjoblom
    Guest

    Re: Countif by Year

    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.
    >
    >

    Reply With QuoteRegister To Reply
  2. 08-31-2005, 01:05 PM #2
    heater
    Guest

    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.


    Reply With QuoteRegister To Reply
  3. 08-31-2005, 06:05 PM #3
    heater
    Guest

    Re: Countif by Year

    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.
    > >
    > >
    >
    >
    Reply With QuoteRegister To Reply
  4. 08-31-2005, 07:05 PM #4
    Peo Sjoblom
    Guest

    Re: Countif by Year

    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.
    >> >
    >> >
    >>
    >>

    Reply With QuoteRegister To Reply
  5. 09-01-2005, 12:05 PM #5
    heater
    Guest

    Re: Countif by Year

    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.
    > >> >
    > >> >
    > >>
    > >>
    >
    >
    Reply With QuoteRegister To Reply
  6. 09-01-2005, 02:05 PM #6
    Dave Peterson
    Guest

    Re: Countif by Year

    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
    Reply With QuoteRegister To Reply
  7. 09-01-2005, 04:05 PM #7
    Dave Peterson
    Guest

    Re: Countif by Year

    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" <[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
    > > > >
    > >
    > > --
    > >
    > > Dave Peterson
    > >

    --

    Dave Peterson
    Reply With QuoteRegister To Reply
  8. 09-01-2005, 04:05 PM #8
    heater
    Guest

    Re: Countif by Year

    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" <[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
    > > >
    >
    > --
    >
    > Dave Peterson
    >
    Reply With QuoteRegister To Reply
  9. 09-01-2005, 04:05 PM #9
    Dave Peterson
    Guest

    Re: Countif by Year

    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" <[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
    > >

    --

    Dave Peterson
    Reply With QuoteRegister To Reply
  10. 09-01-2005, 04:05 PM #10
    heater
    Guest

    Re: Countif by Year

    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" <[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
    >
    Reply With QuoteRegister To Reply
  11. 09-01-2005, 05:05 PM #11
    heater
    Guest

    Re: Countif by Year

    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" <[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
    > > > > >
    > > >
    > > > --
    > > >
    > > > Dave Peterson
    > > >
    >
    > --
    >
    > Dave Peterson
    >
    Reply With QuoteRegister To Reply
  12. 09-01-2005, 05:05 PM #12
    Dave Peterson
    Guest

    Re: Countif by Year

    =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" <[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
    > > > > > >
    > > > >
    > > > > --
    > > > >
    > > > > Dave Peterson
    > > > >
    > >
    > > --
    > >
    > > Dave Peterson
    > >

    --

    Dave Peterson
    Reply With QuoteRegister To Reply
  13. 09-01-2005, 05:05 PM #13
    heater
    Guest

    Re: Countif by Year

    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" <[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
    > > > > > > >
    > > > > >
    > > > > > --
    > > > > >
    > > > > > Dave Peterson
    > > > > >
    > > >
    > > > --
    > > >
    > > > Dave Peterson
    > > >
    >
    > --
    >
    > Dave Peterson
    >
    Reply With QuoteRegister To Reply
  14. 09-01-2005, 05:05 PM #14
    Dave Peterson
    Guest

    Re: Countif by Year

    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" <[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
    > > > > > > > >
    > > > > > >
    > > > > > > --
    > > > > > >
    > > > > > > Dave Peterson
    > > > > > >
    > > > >
    > > > > --
    > > > >
    > > > > Dave Peterson
    > > > >
    > >
    > > --
    > >
    > > Dave Peterson
    > >

    --

    Dave Peterson
    Reply With QuoteRegister To Reply
  15. 09-01-2005, 06:05 PM #15
    heater
    Guest

    Re: Countif by Year

    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" <[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
    > > > > > > > > >
    > > > > > > >
    > > > > > > > --
    > > > > > > >
    > > > > > > > Dave Peterson
    > > > > > > >
    > > > > >
    > > > > > --
    > > > > >
    > > > > > Dave Peterson
    > > > > >
    > > >
    > > > --
    > > >
    > > > Dave Peterson
    > > >
    >
    > --
    >
    > Dave Peterson
    >
    Reply With QuoteRegister To Reply
  16. 09-01-2005, 10:05 PM #16
    Dave Peterson
    Guest

    Re: Countif by Year

    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" <[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
    > > > > > > > > > >
    > > > > > > > >
    > > > > > > > > --
    > > > > > > > >
    > > > > > > > > Dave Peterson
    > > > > > > > >
    > > > > > >
    > > > > > > --
    > > > > > >
    > > > > > > Dave Peterson
    > > > > > >
    > > > >
    > > > > --
    > > > >
    > > > > Dave Peterson
    > > > >
    > >
    > > --
    > >
    > > Dave Peterson
    > >

    --

    Dave Peterson
    Reply With QuoteRegister To Reply
  17. 09-02-2005, 01:05 PM #17
    heater
    Guest

    Re: Countif by Year

    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" <[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
    > > > > > > > > > > >
    > > > > > > > > >
    > > > > > > > > > --
    > > > > > > > > >
    > > > > > > > > > Dave Peterson
    > > > > > > > > >
    > > > > > > >
    > > > > > > > --
    > > > > > > >
    > > > > > > > Dave Peterson
    > > > > > > >
    > > > > >
    > > > > > --
    > > > > >
    > > > > > Dave Peterson
    > > > > >
    > > >
    > > > --
    > > >
    > > > Dave Peterson
    > > >
    >
    > --
    >
    > Dave Peterson
    >
    Reply With QuoteRegister To Reply
  18. 09-02-2005, 02:05 PM #18
    Dave Peterson
    Guest

    Re: Countif by Year

    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" <[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
    > > > > > > > > > > > >
    > > > > > > > > > >
    > > > > > > > > > > --
    > > > > > > > > > >
    > > > > > > > > > > Dave Peterson
    > > > > > > > > > >
    > > > > > > > >
    > > > > > > > > --
    > > > > > > > >
    > > > > > > > > Dave Peterson
    > > > > > > > >
    > > > > > >
    > > > > > > --
    > > > > > >
    > > > > > > Dave Peterson
    > > > > > >
    > > > >
    > > > > --
    > > > >
    > > > > Dave Peterson
    > > > >
    > >
    > > --
    > >
    > > Dave Peterson
    > >

    --

    Dave Peterson
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1