sumproduct problem

[Norbert]

In column "J" I enter certain codes (2 digit numbers), in column "M" I
enter date+time and in column "O" I enter a duration in HH:MM.
I need a formula doing the following:
Whenever there is code 50 in column "J" and the date in column "M" (same
row) is greater than the date in $B$3, then the time in column "O" has
to be added. There can be more than one rows with these conditions
though, I need the summary of all times entered in column "O". Hope it
(I) make sense.

Is that possible? I think it could work with SUMIF(AND(..... or maybe
even better with a SUMPRODUCT formula.

Regards,
Norbert

 

[Arvi Laanemets]

Hi

=SUMPRODUCT(--($J$2:$J$1000=50),--($M$2:$M$1000>$B$3),$O$2:$O$1000)
(Format as Custom "[h]:mm")


Arvi Laanemets

 

[Mike H]

Hi,

Try this woth a Custom format of [hh]:mm


=SUMPRODUCT((J1:J100=50)*(M1:M100=$B$3)*(O1:O100))
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Norbert]

Hi Arvi, hi Mike,
thanks for helping me. My formula looks like this:

=SUMPRODUCT(--('downtime recording'!J4:J93=50),--('downtime
recording'!M4:M93>Date_no+TIME(17,30,0)),'downtime recording'!O4:O93)

or, as Mike suggested

=SUMPRODUCT(('downtime recording'!J4:J93=50)*('downtime
recording'!M4:M93=$B$3)*('downtime recording'!O4:O93))

In both cases, the result I am getting is: 00:00 (I have formatted the
cell as custom [h]:mm)

Norbert

Hi

=SUMPRODUCT(--($J$2:$J$1000=50),--($M$2:$M$1000>$B$3),$O$2:$O$1000)
(Format as Custom "[h]:mm")


Arvi Laanemets

In column "J" I enter certain codes (2 digit numbers), in column "M"
I enter date+time and in column "O" I enter a duration in HH:MM.
I need a formula doing the following:
Whenever there is code 50 in column "J" and the date in column "M"
(same row) is greater than the date in $B$3, then the time in column
"O" has to be added. There can be more than one rows with these
conditions though, I need the summary of all times entered in column
"O". Hope it (I) make sense.

Is that possible? I think it could work with SUMIF(AND(..... or maybe
even better with a SUMPRODUCT formula.

Regards,
Norbert

 

[Norbert]

Sorry guys,

both formulas work. I made a mistake in entering the time into column
"M". The formula should look for the condition where the time is "less,
equal than", not "greater than".

=SUMPRODUCT(--('downtime recording'!J4:J93=50),--('downtime
recording'!M4:M93<=Date_no+TIME(17,30,0)),'downtime recording'!O4:O93)

or, as Mike suggested

=SUMPRODUCT(('downtime recording'!J4:J93=50)*('downtime
recording'!M4:M93<=Date_no+TIME(17,30,0))*('downtime recording'!O4:O93))

Thank you!

Hi Arvi, hi Mike,
thanks for helping me. My formula looks like this:

=SUMPRODUCT(--('downtime recording'!J4:J93=50),--('downtime
recording'!M4:M93>Date_no+TIME(17,30,0)),'downtime recording'!O4:O93)

or, as Mike suggested

=SUMPRODUCT(('downtime recording'!J4:J93=50)*('downtime
recording'!M4:M93=$B$3)*('downtime recording'!O4:O93))

In both cases, the result I am getting is: 00:00 (I have formatted the
cell as custom [h]:mm)

Norbert

Hi

=SUMPRODUCT(--($J$2:$J$1000=50),--($M$2:$M$1000>$B$3),$O$2:$O$1000)
(Format as Custom "[h]:mm")


Arvi Laanemets

In column "J" I enter certain codes (2 digit numbers), in column "M"
I enter date+time and in column "O" I enter a duration in HH:MM.
I need a formula doing the following:
Whenever there is code 50 in column "J" and the date in column "M"
(same row) is greater than the date in $B$3, then the time in column
"O" has to be added. There can be more than one rows with these
conditions though, I need the summary of all times entered in column
"O". Hope it (I) make sense.

Is that possible? I think it could work with SUMIF(AND(..... or
maybe even better with a SUMPRODUCT formula.

Regards,
Norbert