filtering data within data

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

Kat

I have a large database that I am trying to organize and I am trying to count
all of the information based on year and type (there is an example below). I
was able to use the =COUNTIF funtion to get the total number of LO and the
total number of events in 2004, but what formula should I use if I want to
find the number of LO in 2004?
2004 LO
2004 WL
2004 LO
2005 LO
2005 RC
2005 LO
2006 WL
 
Kat said:
I have a large database that I am trying to organize and I am trying to count
all of the information based on year and type (there is an example below). I
was able to use the =COUNTIF funtion to get the total number of LO and the
total number of events in 2004, but what formula should I use if I want to
find the number of LO in 2004?
2004 LO
2004 WL
2004 LO
2005 LO
2005 RC
2005 LO
2006 WL
Click to expand...


Try...

=SUMPRODUCT(--($A$2:$A$8=2004),--($B$2:$B$8="LO"))

Adjust the ranges, accordingly.
 
Tthat worked for non-numeric values, but when I tried to use it with data
that was numeric I got all zeros:

2004 2
2004 2
2004 7
2005 7
2005 3
 
Thanks! Here is another one for you. I am also trying to sort data ranges
by date. the formula I am using is
=SUM(COUNTIF(A:A,"<1000"),-(COUNTIF(A:A,"0"))) to fin the total, how would I
put that into the formula I was using to filter the other data?
 
Is this what you mean?

=SUMPRODUCT(--(SUBTOTAL(9,OFFSET(A2:A100,ROW(A2:A100)-ROW(A2),0,1))<1000)
,--(A2:A100<>0))

Adjust the ranges, accordingly.
 
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 but for some
reason it will not work for 2010.
 
Make sure that the values in Column D that equal 2010 are being
recognized as numerical values. So, for example, if D40 contains 2010,
the following formula should return TRUE

=ISNUMBER(D40)

Does it return TRUE?
 
And an extra question is why you are using the double unary minus when you
are multiplying with the * symbol?
Did you intend either =SUMPRODUCT((D34:D65536=2009)*(G34:G65536=1))
or =SUMPRODUCT(--(D34:D65536=2009),--(G34:G65536=1)) ?
 
Back
Top