Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I am making an Excel 2010 spreadsheet to log support calls, services, and installations that each member would fill out. Due to being paid by job rather than by hour, there is a need for it to count each of these ("Call", "Service", "Install") there is in any given time span. The entry of the data itself would be in Sheet 1, and then a 'splash screen' of sorts would be in Sheet 2. Here I would like to be able to put a date range and it would display how many of each there is. I already can do the COUNTIF statement,

=COUNTIF(Activity!$B:$B,"Call")

but I don't know how to:

a) Add in the prerequisite for a date so it only gets "Calls" within a certain time frame.

b) Take the date it looks for from a cell on the splash screen.

Take for example the following list:

  • 9/1 Call
  • 9/2 Call
  • 9/3 Install
  • 9/4 Call
  • 9/4 Call
  • 9/5 Install
  • 9/5 Install
  • 9/6 Call
  • 9/6 Install
  • 9/7 Call
  • 9/8 Call

(Keep in mind the dates will be in one column, the type will be in another.) I want to be able to have two dates entered (let's say 9/1 and 9/6) to define where to look, and then have a formula to display how many calls and how many installs have been done.

share|improve this question
add comment

1 Answer

up vote 0 down vote accepted

You need to use COUNTIFS. This allows you multiple criteria. As a quick example, if you want to count cells in C1:C100 whose date is between the dates in cells A1 and B1, you'd use:

=COUNTFS(C1:C100,">"&A1,C1:C100,"<"&B1)

Note the quote marks and the use of the ampersand to get the value of the cell.

To add other criteria, the syntax is (... range, criteria, ...) e.g.

=COUNTIFS(C1:C100,">="&A1,C1:C100,"<="&B1, D1:D100, "Calls")

will count celss in C1:C100 are between A1 and B1 AND where D1:D100 = Calls

share|improve this answer
    
This works perfectly for the dates, however my goal is to get "How many of each (call, service, install) are there in this date range?" Your forumla works PERFECTLY for dates. And were I a bit smarter I'm sure I could add in a third criteria, but I can't seem to get it to work by adding [D1:D100,"Calls"]. –  Duall Sep 11 '12 at 3:04
    
Adding ,D1:D100, "Calls" should do it. At least it works for me. What's your exact formula? Can you add a few rows of sample data to the question? –  Rhys Gibson Sep 11 '12 at 3:57
    
Added some example data, though I realized my mistake directly after. I had left it plural as "calls" as opposed to what I actually used, "call". I sure feel silly! Thank you very much! =) –  Duall Sep 11 '12 at 6:12
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.