Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
sumproduct problem
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
sumproduct problem
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" kirjutas sõnumis news: ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
sumproduct problem
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" wrote: 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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
sumproduct problem
Hi Arvi, hi Mike,
thanks for helping me. My formula looks like this: =SUMPRODUCT(--('downtime recording'!J4:J93=50),--('downtime recording'!M4:M93Date_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 On 22-02-10 12:45, Arvi Laanemets wrote: 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" kirjutas sõnumis news: ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
sumproduct problem
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)),'downtim e recording'!O4:O93) or, as Mike suggested =SUMPRODUCT(('downtime recording'!J4:J93=50)*('downtime recording'!M4:M93<=Date_no+TIME(17,30,0))*('downti me recording'!O4:O93)) Thank you! On 22-02-10 13:38, Norbert wrote: Hi Arvi, hi Mike, thanks for helping me. My formula looks like this: =SUMPRODUCT(--('downtime recording'!J4:J93=50),--('downtime recording'!M4:M93Date_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 On 22-02-10 12:45, Arvi Laanemets wrote: 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" kirjutas sõnumis news: ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMPRODUCT Problem | Excel Worksheet Functions | |||
SUMPRODUCT problem | Excel Worksheet Functions | |||
SUMPRODUCT problem | Excel Worksheet Functions | |||
sumproduct problem | Excel Worksheet Functions | |||
SUMPRODUCT Problem | Excel Discussion (Misc queries) |