Tech Support Guy banner

Help with formula

600 Views 3 Replies 2 Participants Last post by  etaf
Good afternoon,

It's been some time since I've posted on here.

I have a spreadsheet which has a number of sheets and one that collates stats from a huge list of data. I use a formula, which looks through the data and returns a number based on number of visits in a month:

=SUMPRODUCT(('All Referrals'!$G:$G>=DATEVALUE("1/7/2017"))*('All Referrals'!$G:$G<=DATEVALUE("31/7/2017")))

The problem is that it isn't counting 1/7/2017 or 31/7/2017. I figure it is because the formula is only looking for dates greater than 1/7/2017 and less than 31/7/2017.

Can someone help with a correction?

Thank you in advance

Not open for further replies.
1 - 4 of 4 Posts
what are you actually summing
these are just the dates
which are correct
will sum column h for those dates

if counting dates
it works ok for 1st and 31st on my sheet

see attached


See less See more
  • Like
Reactions: 1
Hi etaf,

Thanks, having looked further into this because, as you say, the formula should work and include both 1/7/17 and 31/7/17. I realised that the format of the dates in G also have a time attached so 31/7/2017 12:30, once I deleted the time out of the cell, it returned the correct number of referrals in July as 17 and not 16.

Not sure if that makes sense?

But I appreciate your time in helping with this query, thank you

you are welcome
1 - 4 of 4 Posts
Not open for further replies.