PDA

View Full Version : round + sumproduct help



KK1966
01-21-2010, 11:45 PM
Can anyone help me why does canít round with sumproduct formulas as the value in col G is 00:00:00.
Whatís I want to get if the return value = 12:14:54
Can range A1 be come = 12:15 as range A1 format 00:00, but it is resulted 12:14

I try formulas below but it not work


=SUMPRODUCT(ROUND(G3:G500(($A$3:$A$500="Fcode")*($B$3:$B$500="Ftc")*(MOD($F$3:$F$500,1)<"15:00:00"+0),$G$3:G$500),3))

Thanks

Bob Phillips
01-22-2010, 03:55 AM
Your formula is all up the creek, can you give us a workbook to see what you actually mean?

Bob Phillips
01-22-2010, 04:11 AM
Here's a quick quess at what you might want

=ROUND(SUMPRODUCT(($A$3:$A$500="Fcode")*($B$3:$B$500="Ftc")*($F$3:$F$500<"15:00:00"+0),$G$3:G$500)*24*4,0)/4/24