Help with formula

600 Views 3 Replies 2 Participants Last post by etaf, Jan 29, 2018

chrisb1978march

Discussion Starter · Jan 29, 2018

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

Chris

Save

Status: Not open for further replies.

1 - 4 of 4 Posts

etaf

· #2 · Jan 29, 2018

what are you actually summing
these are just the dates
which are correct
ie
=SUMPRODUCT(($G:$G>=DATEVALUE("1/7/2017"))*($G:$G<=DATEVALUE("31/7/2017"))*H:H)
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

Save

Reactions: 1

chrisb1978march

Discussion Starter · #3 · Jan 29, 2018

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

Chris

Save