Below is the data and I am using
=SUMPRODUCT(--(D34:D65536=2004)*(G34:G65536=3))

2004 3
2004 9
2004 3
2004 3
2005 9
2005 3
2005 9
2005 3
2005 3
2005 3
2005 9
2006 3
2006 9
2006 3
2006 3


It is working for every year except 2010.


"Jacob Skaria" wrote:

Please post a sample data (10 to 15 rows) and the formula you tried (which
gives a mismatch). We can work that out.

If this post helps click Yes
---------------
Jacob Skaria


"Kat" wrote:

I figured out the range, but when I try using the formula for simpler things
I keep getting a value of zero...

2006 1
2006 4
2006 5
2006 1

"Jacob Skaria" wrote:

What your formula does is count how many rows are there where col D is having
the number 2009 (not a date) and col G having the number 1
=SUMPRODUCT((D34:D65536=2009)*(G34:G65536=1))

As mentioned in your query if you have date in col D the below will give you
the count of dates which are in the year 2010 and col g with a value of 1.
=SUMPRODUCT((TEXT(D34:D65536,"yyyy")="2010")*(G34: G65536=1))

If this post helps click Yes
---------------
Jacob Skaria


"Kat" wrote:

I am sorting a set of data by date and range:

2006 1473
2006 854
2007 1577
2007 923
2008 2548
2008 1209
2009 1009
2009 2183

I am sorting by <1000, 1000 <1500, 1500 <2000, 2000 and I was able to use
=SUMPRODUCT(--(D34:D65536=2009)*(G34:G65536=1)) to get the results I was
looking for and it worked for 2007, 2008, 2009. I put in some test data to
check and make sure it would continue working when we hit 2010, but for some
reason it will not work. Is there a better formula I should be using? What
did I do?