# Solvedformula 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

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

Chris

#### OBP

If so you could use the month function in that column and then just count the number 4.

#### chrisb1978march

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

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

• 8.4 KB Views: 2

#### chrisb1978march

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

#### OBP

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
I just added a worksheet with it on.

#### chrisb1978march

Thank you, it's easier when i see it like that and yes it works Thank you!!

#### chrisb1978march

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

#### OBP

Will you need to count 2017 as well?

#### OBP

Here is one way to do it, by referencing the date in cell l1.

#### Attachments

• 8.4 KB Views: 3

#### chrisb1978march

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

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

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

#### chrisb1978march

Thanks OBP, it worked, I appreciate your help!!

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.

As Seen On