sumproduct problem
 

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

sumproduct problem
 

Hi,

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

Mike

"Norbert" wrote:

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

sumproduct problem
 

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?

--
__________________________________
HTH

Bob

"Mike H" wrote in message
...
Hi,

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

Mike

"Norbert" wrote:

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

sumproduct problem
 

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 wrote:
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?

sumproduct problem
 

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

--
__________________________________
HTH

Bob

"Norbert" wrote in message
...
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 wrote:
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?

sumproduct problem
 

or

=SUMPRODUCT(--(J2:J20=3),--(N2:N20date_no+"17:30"))

--
__________________________________
HTH

Bob

"Norbert" wrote in message
...
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 wrote:
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?

sumproduct problem
 

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 wrote:
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 wrote:
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?

sumproduct problem
 

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 wrote:
or

=SUMPRODUCT(--(J2:J20=3),--(N2:N20date_no+"17:30"))

sumproduct problem
 

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.

--
__________________________________
HTH

Bob

"Norbert" wrote in message
...
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 wrote:
or

=SUMPRODUCT(--(J2:J20=3),--(N2:N20date_no+"17:30"))

sumproduct problem
 

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(N 1:N10))=A1)*(TIME(HOUR(N1:N10),MINUTE(N1:N10),SECO ND(N1:N10))B1)*(J1:J10=3))

Mike
"Norbert" wrote:

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 wrote:
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 wrote:
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?

sumproduct problem
 

Thanks Mike,
Bob had a simpler way, which works


Mike H wrote:
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(N 1:N10))=A1)*(TIME(HOUR(N1:N10),MINUTE(N1:N10),SECO ND(N1:N10))B1)*(J1:J10=3))

Mike
"Norbert" wrote:


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 wrote:

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 wrote:

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?

sumproduct problem
 

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

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


Bob Phillips wrote:
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.

sumproduct problem
 

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.

--
__________________________________
HTH

Bob

"Norbert" wrote in message
...
you are right, I've changed it to this:
=SUMPRODUCT(--(J3:J300=3),--(N3:N300Date_no+TIME(17,30,0)))
Also looks much better.

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


Bob Phillips wrote:
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.