sumproduct problem

[Norbert]

In column J I have stored different codes (numerical)
for each code a date is stored in column N
There can be more of the same codes in column J

I'd like to know how many times code "3" is found in column J,
whereby the associated date is greater than (date_no+17.5/24)

Can this be done?

Thanks
Norbert

 

[Mike H]

Hi,

I don't understand the format of the date value in column J. Could you
clarify exactly what is in those cells?

Mike

 

[Bob Phillips]

Presumably the 17.5/24 refers to 5:30 PM, but what is date_no and how does
time figure in a series of dates?

 

[Norbert]

the format of the date value in column N is as follows: dd-mm-yy
hh:mm (e.g.: 25-11-08 11:05)
date_no is a name I gave to a certain cell in which I change the date
I'm looking at.
yes 17.5/24 refers to 5:30 PM.

I need to know how often does code no.3 occurs with a date time after
17:30 of certain date I
set in the cell "date_no"

hope it's a bit clearer now.

 

[Bob Phillips]

=SUMPRODUCT(--(J2:J20=3),--(N2:N20>date_no+TIME(17,30,0)))

 

[Bob Phillips]

or

=SUMPRODUCT(--(J2:J20=3),--(N2:N20>date_no+"17:30"))

 

[Norbert]

This is an excerpt from my program:

date_no 25-11-08

column J N
10 25-11-08 12:03
3 25-11-08 10:20
66 25-11-08 10:03
3 25-11-08 19:58*
44 25-11-08 10:42
3 26-11-08 04:16*
11 25-11-08 08:20
26 25-11-08 07:38
3 25-11-08 21:26*

The result I am hoping to get by a formula in this case has to be: 3

 

[Norbert]

Hi Bob,
I changed it slightly and it works.
Thanks a lot.

=SUMPRODUCT(--(J3:J300=3),--(N3:N300>(Date_no+17.5/24)))

PS: what are the two dashes for?

 

[Bob Phillips]

I wouldn't use 17.5/24 personally. Although it will likely make little
difference in this case, fractions are often not accurate in representing
time due to the FP processor in Excel.

 

[Mike H]

Probably much too complicated but try this where A1 id your datevalue and b1
is the time value

=SUMPRODUCT((DATE(YEAR(N1:N10),MONTH(N1:N10),DAY(N1:N10))=A1)*(TIME(HOUR(N1:N10),MINUTE(N1:N10),SECOND(N1:N10))>B1)*(J1:J10=3))

Mike

 

[Norbert]

Thanks Mike,
Bob had a simpler way, which works

Probably much too complicated but try this where A1 id your datevalue and b1
is the time value

=SUMPRODUCT((DATE(YEAR(N1:N10),MONTH(N1:N10),DAY(N1:N10))=A1)*(TIME(HOUR(N1:N10),MINUTE(N1:N10),SECOND(N1:N10))>B1)*(J1:J10=3))

Mike
:

 

[Norbert]

you are right, I've changed it to this:
=SUMPRODUCT(--(J3:J300=3),--(N3:N300>Date_no+TIME(17,30,0)))
Also looks much better.

Can you please explain the "--" in the formula?

 

[Bob Phillips]

It is used to coerce an array of TRUE/FALSE to their 1/0 equivalents. For
instance J3:J300=J3 will return TRUE for each match, FALSE for each
non-match. Thus you get an array like {TRUE,TRUE,FALSE,TRUE,FALSE,...}.
The -- changes that to {1,1,0,1,0,...} which SUMPRODUCT can work with.