Solved formula to pull out number of occurrences in a month

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

chrisb1978march

Thread Starter
Joined
Sep 20, 2007
Messages
194
Good afternoon,

I have received help over the past few years on formulae from a number of very helpful people on here, so thanks once again for that.

I currently have a formula which looks through a spreadsheet of say, 600 entries and returns a number of times a visit has happened in a month. so this is the full formula:

=SUMPRODUCT(('Referrals (April 2018 - date)'!$H:$H>=DATEVALUE("1/4/2018"))*('Referrals (April 2018 - date)'!$H:$H<=DATEVALUE("30/4/2018")))

Is there another way of doing this, i was thinking along the lines of just specifically using the month (April) so rather than looking between 2 dates, it looks for April 2018 and returns the same result?

I just thinking if there is an easier way, so i don't have to change the formula too much for each new year.

I hope i've explained it well enough, but if you have any questions, i'll answer as best as i can :)

Thank you in advance

Chris
 

OBP

Joined
Mar 8, 2005
Messages
19,896
Can you add another column?
If so you could use the month function in that column and then just count the number 4.
 

chrisb1978march

Thread Starter
Joined
Sep 20, 2007
Messages
194
Yes it is possible for me to add another column, I will need to look at how to use the month function (my knowledge is limited and self taught, or from here) but I may even be able to remove the dates and just use month function?... Thanks for you reply and will get back.
 

OBP

Joined
Mar 8, 2005
Messages
19,896
The month function can use the current dates in your spreadsheet column H.
You can add another column, hidden if required where you input and then copy down the column
=month(H2)
You can then count using the count function
Countif(new column range goes here, 4)
would count Aprils
 

Attachments

chrisb1978march

Thread Starter
Joined
Sep 20, 2007
Messages
194
I was just typing a response to say that I don't think it would work, however now that you've explained how it could work, yes that would do it :)

Thank you! makes it easier for me
 

chrisb1978march

Thread Starter
Joined
Sep 20, 2007
Messages
194
I am presuming that you can't distinguish between years?

The reason is that there a couple of years of data in the spreadsheet. So as the MONTH is looking at specifically the months, it will count both 2017 and 2018 and return 4's for both years? And s the only way to do this, to split the years up on to seperate sheets... As that will cause other problems too
 

chrisb1978march

Thread Starter
Joined
Sep 20, 2007
Messages
194
It would need to count 2017 and 2018 but not collectively.

This is to do with a work spreadsheet I have. It may be easier for me to cleanse the data and attach so you can see....however I have now left work.

I'll try and explain a bit more. The spreadsheet is for visits made. The raw data gives names, addresses etc along with date a referral came in but also has another date column for when it was visited.

I have a seperate sheet which then pulls the data from the first sheet and breaks it down to report data so I can see how many referrals were received each month and also how many visits were done. Amongst other things.

So on the second sheet it displays the data from 4 years but only by year not as a whole total. The way it does it at the moment is working fine, but each year I have to amend the formula so it looks for the new year etc.. It is time consuming, so I was wondering if there was an easier way of doing it.

I'm not back at work until Tuesday, but I will attach the spreadsheet so you can see..
 

OBP

Joined
Mar 8, 2005
Messages
19,896
If you look at the new spreadsheet you will see a date in cell l1, if you cahnge the year of the date in that cell it will calculate it for that year.
Or you could have a column for each year.
 

chrisb1978march

Thread Starter
Joined
Sep 20, 2007
Messages
194
Ah yes, a column for each year would work and they'll be hidden so it won't matter how many column there are anyway.

Thanks for your advice and help on this OBP, will try that on Tuesday and report back, byt can't see a reason it wouldn't work. Will mark the thread as 'solved' on Tuesday.

Thanks again :)
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top