G'day excel gurus,
I need to collect stats for the number of referrals each month to our service. As you can see on the attached workbook, I want to collect stats for each month starting in July 2015. On sheet 1 I have referral dates in (dd/mm/yyyy) format. On sheet2, I want to represent the number of referrals in July, august etc.
I'd appreciate the help.
Thanks
Book3.xlsx
Try this formula in A3 (copied across to C3)
Formula:
=COUNTIFS(Sheet1!$A:$A,"<=" & EOMONTH(A$2,0),Sheet1!$A:$A,">=" & EOMONTH(A$2,-1)+1)
Thanks quekbc, your solution worked well on the example that I gave but not at all on my real life workbook. Not sure why but I obviously need to learn more about the EOMONTH function.
alansidman, your solution of adding the =month column is the best solution for me. I'll use this one.
Marked as solved!
thanks!
Last edited by cjharwood; 09-01-2015 at 10:13 PM.
On sheet 1 in column B type =month(A2) and copy down
In cell A3 on sheet 2 type =countif(sheet1!A2:A26,7)
Repeat on Sheet 2 changing the month value.
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
cjharwood, chances are the dates on your real life workbook is currently stored as text rather than actual dates.
Additionally, just as a precaution, know that alansidman's solution is not year dependent. That is, Jun-2015 and Jun-2016 will be counted together.
quekbc, my dates cells are all formatted as dates not text, I suspect I've made an error with the formula somewhere.
You have a good point about the alansidman solution not differentiating between years.... I fiddle with it for a while and see if this becomes an issue.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks