sumproduct problem

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
 
A

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
 
M

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.
 
N

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


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
 
N

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


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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top