Searching for records with multiple criteria

[Jeff]

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!

 

[Max]

.. 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
---

 

[Bernard Liengme]

=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/2005/06/23/download-pivottable-parameters/

best wishes

 

[Joanne]

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="Satisfactory"))

Hope it helps!

 

[Jeff]

Thanks guys!!
--
Jeff

=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/2005/06/23/download-pivottable-parameters/

best wishes

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

 

[Jeff]

What does the % do instead of $?

=SUMPRODUCT(--($A$1:$A$100 = $G2),--($F$1:$F%100=H$1)

 

[David Biddulph]

It shows that even an expert like Bernard can hit the adjacent key on the
keypad occasionally.

 

[Jeff]

Whoops. Didn't mean to call attention to it in that case.

You guys are great!