filtering data within data

[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

 

[Domenic]

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

Try...

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

Adjust the ranges, accordingly.

 

[Eduardo]

Hi,

=SUMPRODUCT(--($A$1:$A$1000=2004)*($B$1:$B$1000="LO"))

 

[Kat]

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

 

[Glenn]

If you are using numbers, don't enclose them in quotes in the formula.

 

[Kat]

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?

 

[Domenic]

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.

 

[Kat]

I was able to use =SUMPRODUCT(--(D3465536=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.

 

[Domenic]

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?

 

[David Biddulph]

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((D3465536=2009)*(G34:G65536=1))
or =SUMPRODUCT(--(D3465536=2009),--(G34:G65536=1)) ?