| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| filtering 3 data row | Excel Discussion (Misc queries) | |||
| Filtering of data - autmatically based on data on other sheet | Excel Discussion (Misc queries) | |||
| Filtering out data | Excel Discussion (Misc queries) | |||
| Help with Filtering data | Excel Discussion (Misc queries) | |||
| Filtering data | New Users to Excel | |||
Linear Mode
