Counting data by certain years

[Guest]

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

 

[Dave R.]

Try
=SUMPRODUCT(--(YEAR(A1:A100)=2003))

 

[Guest]

One way

=SUMPRODUCT(--(YEAR(A2:A10)=2004))


Regards,

Peo Sjoblom

 

[Frank Kabel]

Hi
one way:
=COUNTIF(A1:A100,">=" & DATE(2004,1,1)) - COUNTIF(A1:A100,">=" &
DATE(2005,1,1))

or try
=SUMPRODUCT(--(YEAR(A1:A100)=2004)

 

[Guest]

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

 

[Frank Kabel]

Hi
=SUMPRODUCT(--(YEAR(A2:A10)=2004),--(B2:B10=5))

See:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[Dave R.]

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

 

[Guest]

Ok, change the formula a bit

=SUMPRODUCT(--(YEAR(A2:A10)=2004),--(B2:B10=5))


Regards,

Peo Sjoblom

 

[Guest]

THANK YOU ALL VERY MUCH!! I will give it a go and hopefully not have to
write back!
Thanks again,
SLB