# Excel Countif external date

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.

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

• 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"]. 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! =) Sep 11, 2012 at 6:12

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

• 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. 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 Sep 4, 2016 at 23:55