Jump to content


 


Register a free account to unlock additional features at BleepingComputer.com
Welcome to BleepingComputer, a free community where people like yourself come together to discuss and learn how to use their computers. Using the site is easy and fun. As a guest, you can browse and view the various discussions in the forums, but can not create a new topic or reply to an existing one unless you are logged in. Other benefits of registering an account are subscribing to topics and forums, creating a blog, and having no ads shown anywhere on the site.


Click here to Register a free account now! or read our Welcome Guide to learn how to use this site.

Photo

Spreadsheet rust, please help!


  • Please log in to reply
1 reply to this topic

#1 doctorbob91

doctorbob91

  • Members
  • 27 posts
  • OFFLINE
  •  
  • Local time:03:07 PM

Posted 10 December 2008 - 04:46 PM

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.

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

BC AdBot (Login to Remove)

 


#2 Geoffc

Geoffc

  • Members
  • 89 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:Warragul, Australia
  • Local time:06:07 AM

Posted 14 December 2008 - 09:49 PM

I suggest COUNTIF is too simple for this kind of multiple condition. The Excel help topic "About calculating a value based on a condition" is a good intro to the limitations of similar functions.

If you're stuck with data in this string form (can't present it as a 6-column table, using original spaces as column delimiters), then I'd use a multiple IF-finction on each line/record (matches both conditions, score 1, otherwise 0) and add the 1's.

If you convert it to a table, though, you've easier access to a range of database and array functions that could suit. Have a look at the Excel homepage article http://office.microsoft.com/en-au/excel/HA...=CH100648411033 for guidance on using arrays.
In your case, a table makes the data and any solution a great deal more understandable and traceable.

Time for study!




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users