Need to get counts by date for 2 columns (more details inside)

So basically I have two columns. The first is a list of phone numbers, the second is a list of the dates these numbers were called.

Each phone number (and there are around 150) can have anywhere from a handful of dates called to up to 15000 (only one has over 10k).

So with that in mind, I need a way to get counts of the second column by date ranges. I want it for this past week, the week before that, the week before that, this past month, previous month, ytd etc.....

What's the best way to do this? I've tried messing around with a pivot table and either suck at using it or that's not the solution.

Please help.

Edit: if it matters, the phone number is always listed in the column to the left of the call date column.

Hi, welcome to the forum

Sounds like you need to take a look at using the countifS() function for this?

If you still have a problem, upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

Perhaps you missed my comment regarding...

upload a small (clean) sample workbook (not a pic)

oops Im sorry

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

Ok Done. It should be pretty clear what I'm trying to accomplish and as you will see CountIfs would take forever when in my actual workbook column 1 has 150 different numbers with a random amount of calls per date.

Anyway..let me know...as I'm losing my mind lol.

this is the formula i'm using btw

=SUMPRODUCT((B14:B17>=DATEVALUE("1/1/2010"))*(B14:B17<=DATEVALUE("12/31/2010")))

Follow these steps...
1. Copy column B to a new column (I used C)
2. Highlight that new column, select Data Tab/Remove Duplicates/Continue with Current Selection/Remove Duplicates
In your sample, you will have 3 numbers
(343) 234 - 3214
(832) 376 - 2202
(816) 794 - 2432
3. Change your entry in D1 to 2/1/2014 (a real date)
4. For the rest of your dates, put this in E1 and copy across...
=D1+7
And for the month dates, use 1/1/14 (or whatever you need).

You should end up with a table something like this...

	C	D	E	F	G	H	I
1		1/6/2014	1/13/2014	1/20/2014	1/27/2014	1/1/2014	12/1/2014
2	(343) 234 - 3214	0	0	1	0	1	0
3	(832) 376 - 2202	2	0	0	0	2	1
4	(816) 794 - 2432	0	0	0	0	0	0

D2=COUNTIFS($B$2:$B$35,$C2,$A$2:$A$35,">="&D$1,$A$2:$A$35,"<"&D$1+7)
copied down and across for the weeks

H2=COUNTIFS($B$2:$B$35,$C2,$A$2:$A$35,">="&H$1,$A$2:$A$35,"<"&EOMONTH(H$1,0))
copied down and across for the months

Happy to help

If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neat and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below my 1st post to you)