problem with the formula?

  • Thread starter Thread starter Kat
  • Start date Start date
K

Kat

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?
 
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
 
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
 
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
 
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.
 
There are no 2010 in your sample...

With the below sample the formula will return 3 which means there are 3
entries of 2010 for which a value of 3 is there in Column B
=SUMPRODUCT((A1:A10=2010)*(B1:B10=3))

2010 3
2006 9
2015 3
2009 4
2008 5
2010 3
2008 7
2010 3
2003 3
2002 2
2010 1

If this post helps click Yes
 
Back
Top