| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Searching for records with multiple criteria
It's been a while since I've used Excel and I am struggling to do a countif
with multiple criteria. Perhaps it is the wrong function? On the a spreadsheet, I would like to count the number of times a specific LOCATION receives a specific RATING. Here is a sample of spreadsheet Location From To Review Date Eval Type Rating Silverton 12-Sep-06 9/7/2007 07-SEP-07 Increment Very Good Silverton 9/12/2006 1/12/2007 12-JAN-07 Probationa Very Good College H 8/28/2007 1/28/2007 28-DEC-07 Probationa Satisfactory Riverview 9/12/2006 9/8/2007 08-SEP-07 Increment Very Good Riverview 9/12/2006 1/12/2007 12-JAN-07 Probationa Satisfactory Hughes 11/1/2007 01-NOV-07 BI-Annual Satisfactory Hughes 11/1/2006 01-NOV-06 BI-Annual No Rating Jones 11/1/2004 11/1/2006 01-NOV-06 BI-Annual Satisfactory Carson 11/1/2004 11/1/2006 01-NOV-06 BI-Annual Satisfactory Withrow 11/1/2004 11/1/2006 01-NOV-06 BI-Annual Satisfactory A result would be: Number of "Very Good" at "Silverton" or Number of "Satisfactory" at "Carson" Its a big spread sheet, 450 records (rows) thanks, this should help me get my raise! -- Jeff |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Searching for records with multiple criteria
.. a countif with multiple criteria
Try Sumproduct As an example: Number of "Very Good" at "Silverton" =sumproduct((a2:a500="Silverton")*(f2:f500="Very Good")) -- Max Singapore http://savefile.com/projects/236895 Downloads:21,000 Files:365 Subscribers:65 xdemechanik --- "Jeff" wrote: It's been a while since I've used Excel and I am struggling to do a countif with multiple criteria. Perhaps it is the wrong function? On the a spreadsheet, I would like to count the number of times a specific LOCATION receives a specific RATING. Here is a sample of spreadsheet Location From To Review Date Eval Type Rating Silverton 12-Sep-06 9/7/2007 07-SEP-07 Increment Very Good Silverton 9/12/2006 1/12/2007 12-JAN-07 Probationa Very Good College H 8/28/2007 1/28/2007 28-DEC-07 Probationa Satisfactory Riverview 9/12/2006 9/8/2007 08-SEP-07 Increment Very Good Riverview 9/12/2006 1/12/2007 12-JAN-07 Probationa Satisfactory Hughes 11/1/2007 01-NOV-07 BI-Annual Satisfactory Hughes 11/1/2006 01-NOV-06 BI-Annual No Rating Jones 11/1/2004 11/1/2006 01-NOV-06 BI-Annual Satisfactory Carson 11/1/2004 11/1/2006 01-NOV-06 BI-Annual Satisfactory Withrow 11/1/2004 11/1/2006 01-NOV-06 BI-Annual Satisfactory A result would be: Number of "Very Good" at "Silverton" or Number of "Satisfactory" at "Carson" Its a big spread sheet, 450 records (rows) |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Searching for records with multiple criteria
=SUMPRODUCT(--($A$1:$A$100 ="Silverton"),--($F$1:$F%100="Very Good"))
I added absolute refs so you could copy and edit For more details on SUMPRODUCT Bob Phillips http://www.xldynamic.com/source/xld.SUMPRODUCT.html J.E McGimpsey http://mcgimpsey.com/excel/formulae/doubleneg.html You could make a table with Rating type in H1 to say K1; locations in G2 down to say G10 Then use in H2 =SUMPRODUCT(--($A$1:$A$100 = $G2),--($F$1:$F%100=H$1)) copy across and down best wishes OR learn about Pivot Tables http://www.cpearson.com/excel/pivots.htm http://peltiertech.com/Excel/Pivots/pivotstart.htm http://www.contextures.com/xlPivot02.html http://www.ozgrid.com/Excel/excel-pivot-tables.htm http://www.techonthenet.com/excel/pivottbls/index.htm http://www.dicks-blog.com/archives/2...le-parameters/ best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Jeff" wrote in message ... It's been a while since I've used Excel and I am struggling to do a countif with multiple criteria. Perhaps it is the wrong function? On the a spreadsheet, I would like to count the number of times a specific LOCATION receives a specific RATING. Here is a sample of spreadsheet Location From To Review Date Eval Type Rating Silverton 12-Sep-06 9/7/2007 07-SEP-07 Increment Very Good Silverton 9/12/2006 1/12/2007 12-JAN-07 Probationa Very Good College H 8/28/2007 1/28/2007 28-DEC-07 Probationa Satisfactory Riverview 9/12/2006 9/8/2007 08-SEP-07 Increment Very Good Riverview 9/12/2006 1/12/2007 12-JAN-07 Probationa Satisfactory Hughes 11/1/2007 01-NOV-07 BI-Annual Satisfactory Hughes 11/1/2006 01-NOV-06 BI-Annual No Rating Jones 11/1/2004 11/1/2006 01-NOV-06 BI-Annual Satisfactory Carson 11/1/2004 11/1/2006 01-NOV-06 BI-Annual Satisfactory Withrow 11/1/2004 11/1/2006 01-NOV-06 BI-Annual Satisfactory A result would be: Number of "Very Good" at "Silverton" or Number of "Satisfactory" at "Carson" Its a big spread sheet, 450 records (rows) thanks, this should help me get my raise! -- Jeff |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Searching for records with multiple criteria
Jeff
I would use sumproduct for this For the eg of "Number of 'Satisfactory' at 'Carson' Location " Assuming Location = colunm A, and rating = colunm F, and first row of data = 1, and last row of data = 450, Try: =SUMPRODUCT((A1:A450="Carson")*(F1:F450="Satisfact ory")) Hope it helps! "Jeff" wrote: It's been a while since I've used Excel and I am struggling to do a countif with multiple criteria. Perhaps it is the wrong function? On the a spreadsheet, I would like to count the number of times a specific LOCATION receives a specific RATING. Here is a sample of spreadsheet Location From To Review Date Eval Type Rating Silverton 12-Sep-06 9/7/2007 07-SEP-07 Increment Very Good Silverton 9/12/2006 1/12/2007 12-JAN-07 Probationa Very Good College H 8/28/2007 1/28/2007 28-DEC-07 Probationa Satisfactory Riverview 9/12/2006 9/8/2007 08-SEP-07 Increment Very Good Riverview 9/12/2006 1/12/2007 12-JAN-07 Probationa Satisfactory Hughes 11/1/2007 01-NOV-07 BI-Annual Satisfactory Hughes 11/1/2006 01-NOV-06 BI-Annual No Rating Jones 11/1/2004 11/1/2006 01-NOV-06 BI-Annual Satisfactory Carson 11/1/2004 11/1/2006 01-NOV-06 BI-Annual Satisfactory Withrow 11/1/2004 11/1/2006 01-NOV-06 BI-Annual Satisfactory A result would be: Number of "Very Good" at "Silverton" or Number of "Satisfactory" at "Carson" Its a big spread sheet, 450 records (rows) thanks, this should help me get my raise! -- Jeff |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Searching for records with multiple criteria
Thanks guys!!
-- Jeff "Bernard Liengme" wrote: =SUMPRODUCT(--($A$1:$A$100 ="Silverton"),--($F$1:$F%100="Very Good")) I added absolute refs so you could copy and edit For more details on SUMPRODUCT Bob Phillips http://www.xldynamic.com/source/xld.SUMPRODUCT.html J.E McGimpsey http://mcgimpsey.com/excel/formulae/doubleneg.html You could make a table with Rating type in H1 to say K1; locations in G2 down to say G10 Then use in H2 =SUMPRODUCT(--($A$1:$A$100 = $G2),--($F$1:$F%100=H$1)) copy across and down best wishes OR learn about Pivot Tables http://www.cpearson.com/excel/pivots.htm http://peltiertech.com/Excel/Pivots/pivotstart.htm http://www.contextures.com/xlPivot02.html http://www.ozgrid.com/Excel/excel-pivot-tables.htm http://www.techonthenet.com/excel/pivottbls/index.htm http://www.dicks-blog.com/archives/2...le-parameters/ best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Jeff" wrote in message ... It's been a while since I've used Excel and I am struggling to do a countif with multiple criteria. Perhaps it is the wrong function? On the a spreadsheet, I would like to count the number of times a specific LOCATION receives a specific RATING. Here is a sample of spreadsheet Location From To Review Date Eval Type Rating Silverton 12-Sep-06 9/7/2007 07-SEP-07 Increment Very Good Silverton 9/12/2006 1/12/2007 12-JAN-07 Probationa Very Good College H 8/28/2007 1/28/2007 28-DEC-07 Probationa Satisfactory Riverview 9/12/2006 9/8/2007 08-SEP-07 Increment Very Good Riverview 9/12/2006 1/12/2007 12-JAN-07 Probationa Satisfactory Hughes 11/1/2007 01-NOV-07 BI-Annual Satisfactory Hughes 11/1/2006 01-NOV-06 BI-Annual No Rating Jones 11/1/2004 11/1/2006 01-NOV-06 BI-Annual Satisfactory Carson 11/1/2004 11/1/2006 01-NOV-06 BI-Annual Satisfactory Withrow 11/1/2004 11/1/2006 01-NOV-06 BI-Annual Satisfactory A result would be: Number of "Very Good" at "Silverton" or Number of "Satisfactory" at "Carson" Its a big spread sheet, 450 records (rows) thanks, this should help me get my raise! -- Jeff |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Searching for records with multiple criteria
What does the % do instead of $?
=SUMPRODUCT(--($A$1:$A$100 = $G2),--($F$1:$F%100=H$1) -- Jeff "Bernard Liengme" wrote: =SUMPRODUCT(--($A$1:$A$100 ="Silverton"),--($F$1:$F%100="Very Good")) I added absolute refs so you could copy and edit For more details on SUMPRODUCT Bob Phillips http://www.xldynamic.com/source/xld.SUMPRODUCT.html J.E McGimpsey http://mcgimpsey.com/excel/formulae/doubleneg.html You could make a table with Rating type in H1 to say K1; locations in G2 down to say G10 Then use in H2 =SUMPRODUCT(--($A$1:$A$100 = $G2),--($F$1:$F%100=H$1)) copy across and down best wishes OR learn about Pivot Tables http://www.cpearson.com/excel/pivots.htm http://peltiertech.com/Excel/Pivots/pivotstart.htm http://www.contextures.com/xlPivot02.html http://www.ozgrid.com/Excel/excel-pivot-tables.htm http://www.techonthenet.com/excel/pivottbls/index.htm http://www.dicks-blog.com/archives/2...le-parameters/ best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Jeff" wrote in message ... It's been a while since I've used Excel and I am struggling to do a countif with multiple criteria. Perhaps it is the wrong function? On the a spreadsheet, I would like to count the number of times a specific LOCATION receives a specific RATING. Here is a sample of spreadsheet Location From To Review Date Eval Type Rating Silverton 12-Sep-06 9/7/2007 07-SEP-07 Increment Very Good Silverton 9/12/2006 1/12/2007 12-JAN-07 Probationa Very Good College H 8/28/2007 1/28/2007 28-DEC-07 Probationa Satisfactory Riverview 9/12/2006 9/8/2007 08-SEP-07 Increment Very Good Riverview 9/12/2006 1/12/2007 12-JAN-07 Probationa Satisfactory Hughes 11/1/2007 01-NOV-07 BI-Annual Satisfactory Hughes 11/1/2006 01-NOV-06 BI-Annual No Rating Jones 11/1/2004 11/1/2006 01-NOV-06 BI-Annual Satisfactory Carson 11/1/2004 11/1/2006 01-NOV-06 BI-Annual Satisfactory Withrow 11/1/2004 11/1/2006 01-NOV-06 BI-Annual Satisfactory A result would be: Number of "Very Good" at "Silverton" or Number of "Satisfactory" at "Carson" Its a big spread sheet, 450 records (rows) thanks, this should help me get my raise! -- Jeff |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Searching for records with multiple criteria
It shows that even an expert like Bernard can hit the adjacent key on the
keypad occasionally. :-) -- David Biddulph "Jeff" wrote in message ... What does the % do instead of $? =SUMPRODUCT(--($A$1:$A$100 = $G2),--($F$1:$F%100=H$1) -- Jeff "Bernard Liengme" wrote: =SUMPRODUCT(--($A$1:$A$100 ="Silverton"),--($F$1:$F%100="Very Good")) I added absolute refs so you could copy and edit For more details on SUMPRODUCT Bob Phillips http://www.xldynamic.com/source/xld.SUMPRODUCT.html J.E McGimpsey http://mcgimpsey.com/excel/formulae/doubleneg.html You could make a table with Rating type in H1 to say K1; locations in G2 down to say G10 Then use in H2 =SUMPRODUCT(--($A$1:$A$100 = $G2),--($F$1:$F%100=H$1)) copy across and down best wishes OR learn about Pivot Tables http://www.cpearson.com/excel/pivots.htm http://peltiertech.com/Excel/Pivots/pivotstart.htm http://www.contextures.com/xlPivot02.html http://www.ozgrid.com/Excel/excel-pivot-tables.htm http://www.techonthenet.com/excel/pivottbls/index.htm http://www.dicks-blog.com/archives/2...le-parameters/ best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Jeff" wrote in message ... It's been a while since I've used Excel and I am struggling to do a countif with multiple criteria. Perhaps it is the wrong function? On the a spreadsheet, I would like to count the number of times a specific LOCATION receives a specific RATING. Here is a sample of spreadsheet Location From To Review Date Eval Type Rating Silverton 12-Sep-06 9/7/2007 07-SEP-07 Increment Very Good Silverton 9/12/2006 1/12/2007 12-JAN-07 Probationa Very Good College H 8/28/2007 1/28/2007 28-DEC-07 Probationa Satisfactory Riverview 9/12/2006 9/8/2007 08-SEP-07 Increment Very Good Riverview 9/12/2006 1/12/2007 12-JAN-07 Probationa Satisfactory Hughes 11/1/2007 01-NOV-07 BI-Annual Satisfactory Hughes 11/1/2006 01-NOV-06 BI-Annual No Rating Jones 11/1/2004 11/1/2006 01-NOV-06 BI-Annual Satisfactory Carson 11/1/2004 11/1/2006 01-NOV-06 BI-Annual Satisfactory Withrow 11/1/2004 11/1/2006 01-NOV-06 BI-Annual Satisfactory A result would be: Number of "Very Good" at "Silverton" or Number of "Satisfactory" at "Carson" Its a big spread sheet, 450 records (rows) thanks, this should help me get my raise! -- Jeff |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Searching for records with multiple criteria
Whoops. Didn't mean to call attention to it in that case.
You guys are great! -- Jeff "David Biddulph" wrote: It shows that even an expert like Bernard can hit the adjacent key on the keypad occasionally. :-) -- David Biddulph "Jeff" wrote in message ... What does the % do instead of $? =SUMPRODUCT(--($A$1:$A$100 = $G2),--($F$1:$F%100=H$1) -- Jeff "Bernard Liengme" wrote: =SUMPRODUCT(--($A$1:$A$100 ="Silverton"),--($F$1:$F%100="Very Good")) I added absolute refs so you could copy and edit For more details on SUMPRODUCT Bob Phillips http://www.xldynamic.com/source/xld.SUMPRODUCT.html J.E McGimpsey http://mcgimpsey.com/excel/formulae/doubleneg.html You could make a table with Rating type in H1 to say K1; locations in G2 down to say G10 Then use in H2 =SUMPRODUCT(--($A$1:$A$100 = $G2),--($F$1:$F%100=H$1)) copy across and down best wishes OR learn about Pivot Tables http://www.cpearson.com/excel/pivots.htm http://peltiertech.com/Excel/Pivots/pivotstart.htm http://www.contextures.com/xlPivot02.html http://www.ozgrid.com/Excel/excel-pivot-tables.htm http://www.techonthenet.com/excel/pivottbls/index.htm http://www.dicks-blog.com/archives/2...le-parameters/ best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Jeff" wrote in message ... It's been a while since I've used Excel and I am struggling to do a countif with multiple criteria. Perhaps it is the wrong function? On the a spreadsheet, I would like to count the number of times a specific LOCATION receives a specific RATING. Here is a sample of spreadsheet Location From To Review Date Eval Type Rating Silverton 12-Sep-06 9/7/2007 07-SEP-07 Increment Very Good Silverton 9/12/2006 1/12/2007 12-JAN-07 Probationa Very Good College H 8/28/2007 1/28/2007 28-DEC-07 Probationa Satisfactory Riverview 9/12/2006 9/8/2007 08-SEP-07 Increment Very Good Riverview 9/12/2006 1/12/2007 12-JAN-07 Probationa Satisfactory Hughes 11/1/2007 01-NOV-07 BI-Annual Satisfactory Hughes 11/1/2006 01-NOV-06 BI-Annual No Rating Jones 11/1/2004 11/1/2006 01-NOV-06 BI-Annual Satisfactory Carson 11/1/2004 11/1/2006 01-NOV-06 BI-Annual Satisfactory Withrow 11/1/2004 11/1/2006 01-NOV-06 BI-Annual Satisfactory A result would be: Number of "Very Good" at "Silverton" or Number of "Satisfactory" at "Carson" Its a big spread sheet, 450 records (rows) thanks, this should help me get my raise! -- Jeff |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Searching and displaying records that match criteria. | Excel Discussion (Misc queries) | |||
| Filtering for Unique Records with multiple-column criteria | Excel Discussion (Misc queries) | |||
| Searching rows with multiple columns criteria | Excel Worksheet Functions | |||
| return multiple records matching multiple criteria | Excel Worksheet Functions | |||
| Show top five records based on meeting multiple criteria | Excel Worksheet Functions | |||
Linear Mode
