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
|