sumproduct problem

  • Thread starter Thread starter Norbert
  • Start date Start date
N

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
 
Hi,

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

Mike
 
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?
 
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.
 
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
 
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?
 
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.
 
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
 
Thanks Mike,
Bob had a simpler way, which works


Mike said:
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
:
Click to expand...
 
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?
 
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.
 
Back
Top