2

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.

2 Answers 2

1

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

3
  • 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, 2012 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? Sep 11, 2012 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, 2012 at 6:12
-1

I think the best way would be to use if and And. You will need to add two columns one for "Calls" and one for "Installs".

enter image description here

3
  • Realistically, nobody is going to download an unknown file, especially from an unknown user. That leaves just a hint rather than a solution. The way to post an Excel solution is to post a link to a screenshot, include the essential formulas in the answer, and include explanation of how the formulas work. Browse some of the other Excel answers on the site to see how it's done.
    – fixer1234
    Sep 4, 2016 at 21:26
  • Fixer1234 that is your opinion and I respect it. It's not like I'm going to gain out of helping our friend here. I provided the files to make it easy for him/her to understand the answer. I also did it because I love MS excel and have been using it for over 25 years. During all these years using excel I learned that the best way to help people is to provide a useful example. Thanks
    – rhoula
    Sep 4, 2016 at 23:55
  • You're absolutely right, only using an approach that isn't particularly compatible with this site. In general, links to answers elsewhere are discouraged because links eventually break and then the answers aren't useful. The meat of your answer is contained in an external link. That's compounded by the fact that it isn't even a link to something that can be viewed on-screen. It requires downloading an unknown file that people will consider could potentially contain malware, so it isn't in a form that people can/will use.
    – fixer1234
    Sep 5, 2016 at 0:51

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .