Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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" wrote in message ... 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. |
#3
|
|||
|
|||
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" wrote in message ... 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. |
#4
|
|||
|
|||
Change it to
=SUMPRODUCT((YEAR(A2:A86)=1996)*(AE2:AO86)) -- Regards, Peo Sjoblom (No private emails please) "heater" wrote in message ... 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" wrote in message ... 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. |
#5
|
|||
|
|||
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" wrote in message ... 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" wrote in message ... 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. |
#6
|
|||
|
|||
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" wrote in message ... 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" wrote in message ... 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 |
#7
|
|||
|
|||
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" wrote in message ... 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" wrote in message ... 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 |
#8
|
|||
|
|||
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" wrote in message ... 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" wrote in message ... 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 |
#9
|
|||
|
|||
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" wrote in message ... 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" wrote in message ... 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 |
#10
|
|||
|
|||
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" wrote in message ... 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" wrote in message ... 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 |
#11
|
|||
|
|||
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" wrote in message ... 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" wrote in message ... 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 |
#12
|
|||
|
|||
=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" wrote in message ... 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" wrote in message ... 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 |
#13
|
|||
|
|||
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" wrote in message ... 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" wrote in message ... 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 |
#14
|
|||
|
|||
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" wrote in message ... 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" wrote in message ... 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 |
#15
|
|||
|
|||
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" wrote in message ... 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" wrote in message ... 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 |
#16
|
|||
|
|||
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" wrote in message ... 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" wrote in message ... 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 |
#17
|
|||
|
|||
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" wrote in message ... 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" wrote in message ... 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 |
#18
|
|||
|
|||
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" wrote in message ... 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" wrote in message ... 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto Year scale | Charts and Charting in Excel | |||
How to compare current year to prior year in bar chart? | Charts and Charting in Excel | |||
Combining IF and COUNTIF based on two columns | Excel Discussion (Misc queries) | |||
countif number of occurences per month per year. | Excel Worksheet Functions | |||
Countif - Countif | Excel Worksheet Functions |