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

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

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

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

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

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

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

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

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