sumproduct problem

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

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
 
Hi

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


Arvi Laanemets
 
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.
 
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


Norbert said:
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
Click to expand...
Click to expand...
 
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


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