SUMPRODUCT - Bit Confused

[Darren]

Hello.

I have the following data set out as follows in a
worksheet:

Date received Date Sent Difference in Days
01/12/03 08/12/03 7 (=Date sent-Date received)
01/12/03

I am attempting to use SUMPRODUCT to tell me how many
entries I have for example, in the month of December where
the difference in days is equal to or below 7. Using the
following calculation it is only capturing the dates in
December and seems to be ignoring the difference in days
argument:

=SUMPRODUCT(('Western 2003'!K1:K24<=7)*('Western 2003'!
H1:H24>=DATE(2003,12,1))*(('Western 2003'!H1:H24<=DATE
(2003,12,31))))

Any ideas?

 

[Andy B]

Darren

Try this:

=SUMPRODUCT(('Western 2003'!K1:K24<=7)*('Western
2003'!H1:H24>=DATE(2003,12,1))*('Western 2003'!H1:H24<=DATE(2003,12,31)))

Andy.

 

[Darren]

Thanks Andy. Tried it, but I'm getting the same answer,
it's still caculating the number of months and ignoring
the <=7 days.

Strange :S

 

[Bob Phillips]

Darren,

You aren't testing for <= 7 days, so it can't allow for that. I am not sure
you are testing for December correctly (why not test J1J24 at all), but
assuming it's what you want, try this

=SUMPRODUCT(('Western 2003'! H1:H24>=DATE(2003,12,1))*('Western
2003'!H1:H24<=DATE(2003,12,31))*(L1:L24-K1:K24<=7))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

forgot to adjust all of my formula

=SUMPRODUCT(('Western 2003'! H1:H24>=DATE(2003,12,1))*('Western
2003'!H1:H24<=DATE(2003,12,31))*(I1:I24-H1:H24<=7))


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)