Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Counting data by certain years
Hello,
Here is my issue. I have a bunch of dates in one column that may have different years. In another column I have the data that I need to count. What I want to do is count the data by year. Here's my example to clarify: Column A Column B 2/10/2003 8 3/1/2004 5 1/4/2004 4 5/4/2002 10 I want to count how many answers there in Column B that were entered in the year 2003 (from Column A). In this case, there's 1. Then I want to count how many answers there are in Column B that were entered in the year 2004 (from Column A). In this case, there's 2. Etc. Please let me know if you can help me out with this! Thanks!!! |
#2
|
|||
|
|||
Try
=SUMPRODUCT(--(YEAR(A1:A100)=2003)) "SLB" wrote in message ... Hello, Here is my issue. I have a bunch of dates in one column that may have different years. In another column I have the data that I need to count. What I want to do is count the data by year. Here's my example to clarify: Column A Column B 2/10/2003 8 3/1/2004 5 1/4/2004 4 5/4/2002 10 I want to count how many answers there in Column B that were entered in the year 2003 (from Column A). In this case, there's 1. Then I want to count how many answers there are in Column B that were entered in the year 2004 (from Column A). In this case, there's 2. Etc. Please let me know if you can help me out with this! Thanks!!! |
#3
|
|||
|
|||
One way
=SUMPRODUCT(--(YEAR(A2:A10)=2004)) Regards, Peo Sjoblom "SLB" wrote: Hello, Here is my issue. I have a bunch of dates in one column that may have different years. In another column I have the data that I need to count. What I want to do is count the data by year. Here's my example to clarify: Column A Column B 2/10/2003 8 3/1/2004 5 1/4/2004 4 5/4/2002 10 I want to count how many answers there in Column B that were entered in the year 2003 (from Column A). In this case, there's 1. Then I want to count how many answers there are in Column B that were entered in the year 2004 (from Column A). In this case, there's 2. Etc. Please let me know if you can help me out with this! Thanks!!! |
#4
|
|||
|
|||
Hi
one way: =COUNTIF(A1:A100,"=" & DATE(2004,1,1)) - COUNTIF(A1:A100,"=" & DATE(2005,1,1)) or try =SUMPRODUCT(--(YEAR(A1:A100)=2004) -- Regards Frank Kabel Frankfurt, Germany "SLB" schrieb im Newsbeitrag ... Hello, Here is my issue. I have a bunch of dates in one column that may have different years. In another column I have the data that I need to count. What I want to do is count the data by year. Here's my example to clarify: Column A Column B 2/10/2003 8 3/1/2004 5 1/4/2004 4 5/4/2002 10 I want to count how many answers there in Column B that were entered in the year 2003 (from Column A). In this case, there's 1. Then I want to count how many answers there are in Column B that were entered in the year 2004 (from Column A). In this case, there's 2. Etc. Please let me know if you can help me out with this! Thanks!!! |
#5
|
|||
|
|||
This sounds good but in some cases, I want to count how many cells in Column
B contain the number 5 if the year in Column A is 2004. So in my example ther would be 1. So I guess there would be a condition for Column A and Column B "Peo Sjoblom" wrote: One way =SUMPRODUCT(--(YEAR(A2:A10)=2004)) Regards, Peo Sjoblom "SLB" wrote: Hello, Here is my issue. I have a bunch of dates in one column that may have different years. In another column I have the data that I need to count. What I want to do is count the data by year. Here's my example to clarify: Column A Column B 2/10/2003 8 3/1/2004 5 1/4/2004 4 5/4/2002 10 I want to count how many answers there in Column B that were entered in the year 2003 (from Column A). In this case, there's 1. Then I want to count how many answers there are in Column B that were entered in the year 2004 (from Column A). In this case, there's 2. Etc. Please let me know if you can help me out with this! Thanks!!! |
#6
|
|||
|
|||
Hi
=SUMPRODUCT(--(YEAR(A2:A10)=2004),--(B2:B10=5)) See: http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- Regards Frank Kabel Frankfurt, Germany "SLB" schrieb im Newsbeitrag ... This sounds good but in some cases, I want to count how many cells in Column B contain the number 5 if the year in Column A is 2004. So in my example ther would be 1. So I guess there would be a condition for Column A and Column B "Peo Sjoblom" wrote: One way =SUMPRODUCT(--(YEAR(A2:A10)=2004)) Regards, Peo Sjoblom "SLB" wrote: Hello, Here is my issue. I have a bunch of dates in one column that may have different years. In another column I have the data that I need to count. What I want to do is count the data by year. Here's my example to clarify: Column A Column B 2/10/2003 8 3/1/2004 5 1/4/2004 4 5/4/2002 10 I want to count how many answers there in Column B that were entered in the year 2003 (from Column A). In this case, there's 1. Then I want to count how many answers there are in Column B that were entered in the year 2004 (from Column A). In this case, there's 2. Etc. Please let me know if you can help me out with this! Thanks!!! |
#7
|
|||
|
|||
You can add conditions to the sumproduct formula such as
=SUMPRODUCT(--(YEAR(A1:A100)=2004),--(--B1:B100=5)) or you could dump some of the first part and use; =SUMPRODUCT((YEAR(A1:A100)=2004)*(--B1:B100=5)) "SLB" wrote in message ... This sounds good but in some cases, I want to count how many cells in Column B contain the number 5 if the year in Column A is 2004. So in my example ther would be 1. So I guess there would be a condition for Column A and Column B "Peo Sjoblom" wrote: One way =SUMPRODUCT(--(YEAR(A2:A10)=2004)) Regards, Peo Sjoblom "SLB" wrote: Hello, Here is my issue. I have a bunch of dates in one column that may have different years. In another column I have the data that I need to count. What I want to do is count the data by year. Here's my example to clarify: Column A Column B 2/10/2003 8 3/1/2004 5 1/4/2004 4 5/4/2002 10 I want to count how many answers there in Column B that were entered in the year 2003 (from Column A). In this case, there's 1. Then I want to count how many answers there are in Column B that were entered in the year 2004 (from Column A). In this case, there's 2. Etc. Please let me know if you can help me out with this! Thanks!!! |
#8
|
|||
|
|||
Ok, change the formula a bit
=SUMPRODUCT(--(YEAR(A2:A10)=2004),--(B2:B10=5)) Regards, Peo Sjoblom "SLB" wrote: This sounds good but in some cases, I want to count how many cells in Column B contain the number 5 if the year in Column A is 2004. So in my example ther would be 1. So I guess there would be a condition for Column A and Column B "Peo Sjoblom" wrote: One way =SUMPRODUCT(--(YEAR(A2:A10)=2004)) Regards, Peo Sjoblom "SLB" wrote: Hello, Here is my issue. I have a bunch of dates in one column that may have different years. In another column I have the data that I need to count. What I want to do is count the data by year. Here's my example to clarify: Column A Column B 2/10/2003 8 3/1/2004 5 1/4/2004 4 5/4/2002 10 I want to count how many answers there in Column B that were entered in the year 2003 (from Column A). In this case, there's 1. Then I want to count how many answers there are in Column B that were entered in the year 2004 (from Column A). In this case, there's 2. Etc. Please let me know if you can help me out with this! Thanks!!! |
#9
|
|||
|
|||
THANK YOU ALL VERY MUCH!! I will give it a go and hopefully not have to
write back! Thanks again, SLB "Peo Sjoblom" wrote: Ok, change the formula a bit =SUMPRODUCT(--(YEAR(A2:A10)=2004),--(B2:B10=5)) Regards, Peo Sjoblom "SLB" wrote: This sounds good but in some cases, I want to count how many cells in Column B contain the number 5 if the year in Column A is 2004. So in my example ther would be 1. So I guess there would be a condition for Column A and Column B "Peo Sjoblom" wrote: One way =SUMPRODUCT(--(YEAR(A2:A10)=2004)) Regards, Peo Sjoblom "SLB" wrote: Hello, Here is my issue. I have a bunch of dates in one column that may have different years. In another column I have the data that I need to count. What I want to do is count the data by year. Here's my example to clarify: Column A Column B 2/10/2003 8 3/1/2004 5 1/4/2004 4 5/4/2002 10 I want to count how many answers there in Column B that were entered in the year 2003 (from Column A). In this case, there's 1. Then I want to count how many answers there are in Column B that were entered in the year 2004 (from Column A). In this case, there's 2. Etc. Please let me know if you can help me out with this! Thanks!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with data not getting plotted | Excel Discussion (Misc queries) | |||
Help with data not getting plotted | Charts and Charting in Excel | |||
Counting Rainfall Data | Excel Discussion (Misc queries) | |||
Charting data ranges that change | Charts and Charting in Excel | |||
Running Data Table using an input that triggers DDE linked data | Excel Discussion (Misc queries) |