filtering data within data
 

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

filtering data within data
 

In article ,
Kat wrote:

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.

--
Domenic
Microsoft Excel MVP
www.xl-central.com
Your Quick Reference to Excel Solutions

filtering data within data
 

Hi,

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

"Kat" wrote:

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

filtering data within data
 

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


"Eduardo" wrote:

Hi,

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

"Kat" wrote:

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

filtering data within data
 

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

Kat wrote:
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


"Eduardo" wrote:

Hi,

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

"Kat" wrote:

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

filtering data within data
 

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?

"Glenn" wrote:

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

Kat wrote:
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


"Eduardo" wrote:

Hi,

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

"Kat" wrote:

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

filtering data within 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.

--
Domenic
Microsoft Excel MVP
www.xl-central.com
Your Quick Reference to Excel Solutions

In article ,
Kat wrote:

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?

"Glenn" wrote:

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

Kat wrote:
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


"Eduardo" wrote:

Hi,

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

"Kat" wrote:

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

filtering data within data
 

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.

"Domenic" wrote:

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.

--
Domenic
Microsoft Excel MVP
www.xl-central.com
Your Quick Reference to Excel Solutions

In article ,
Kat wrote:

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?

"Glenn" wrote:

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

Kat wrote:
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


"Eduardo" wrote:

Hi,

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

"Kat" wrote:

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

filtering data within data
 

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?

--
Domenic
Microsoft Excel MVP
www.xl-central.com
Your Quick Reference to Excel Solutions

In article ,
Kat wrote:

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.

"Domenic" wrote:

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.

--
Domenic
Microsoft Excel MVP
www.xl-central.com
Your Quick Reference to Excel Solutions

In article ,
Kat wrote:

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?

"Glenn" wrote:

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

Kat wrote:
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


"Eduardo" wrote:

Hi,

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

"Kat" wrote:

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

filtering data within data
 

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)) ?
--
David Biddulph

"Domenic" wrote in message
...
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?

--
Domenic
Microsoft Excel MVP
www.xl-central.com
Your Quick Reference to Excel Solutions

In article ,
Kat wrote:

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.

"Domenic" wrote:

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.

--
Domenic
Microsoft Excel MVP
www.xl-central.com
Your Quick Reference to Excel Solutions

In article ,
Kat wrote:

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?

"Glenn" wrote:

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

Kat wrote:
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


"Eduardo" wrote:

Hi,

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

"Kat" wrote:

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