need another formula for a spreadsheet at work

[cstrait]

I have a spreadsheet with three columns. The first are the names of the customer service reps (example: John Smith). The second column is the subject of the phone call (example: outbound call). The third is the date (example: 11/12/2012). I need to count the number of calls for a specific rep on a specific date. For example: John Smith had 22 calls on Monday. So the row would have three conditions to be met in order to be counted. 1, Specific name of rep "John Smith" for a column. 2, Specific date "11/12/2012". And 3, there must be text present (i.e. "inbound call" or "outbound call") in the subject column; if no text is present, it's not counted. Hope someone knows a formula for this.

[djapigo]

cstrait,

I suggest you use Pivot Tables... simply highlight your range, click on Pivot Tables (Insert tab), then simply drop Name and Date on the "rows" section, the text present in the column or row section (depends how you want to see it)... then add the text present in the values sections (should say Count of...)

Hope this is what you are looking for...

[cstrait]

Nah, I really need a formula. Thank though.

[oeldere]

I suggest you use Pivot Tables

[cstrait]

what if i did it based on two of three columns. the criteria would just be customer service rep on a specific date. like 11/12/2012 and "John Smith" have to be met to count.

[oeldere]

You get better help if you post an Excel example, without confidentional information.

If you also post the desired result it would be great.

[djapigo]

Hi cstrait,

You can use the COUNTIFS function if you insist on using a formula...

[cstrait]

=COUNTIFS('Calls In Out'!L1:L100000,"Dan Lipsher")*('Calls In Out'!B1:B100000,"11/12/2012")

Would that work? I want all rows counted which include both "Dan Lipsher" on column L and "11/12/2012" on column B. This formula isn't working but something similar would really do the trick.

[oeldere]

Thats the syntax for a sumproduct formula.

See for the syntax of sumifs F1 (helpfunction).

[djapigo]

oeldere is correct... try this...

=COUNTIFS('Calls In Out'!$L$1:$L$100000,"Dan Lipsher",'Calls In Out'!$B$1:$B$100000,"11/12/2012")
or
=COUNTIFS('Calls In Out'!$L$1:$L$100000,"Dan Lipsher",'Calls In Out'!$B$1:$B$100000,DATE(2012,11,12))

Couple of things to note:
1. You should fix the ranges (absolute references, dollar signs)
2. I'm assuming either your dates are text (use the first formula) or real dates (then use the 2nd formula)...
3. No multiplication needed (as oeldere alluded to), just separate everything by commas... (follow the formula as you are writing it out...)

Hope this helps...

[FDibbins]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.