Sumproduct with Time Duration calculation

**gav0101** · 04-05-2012, 12:28 PM

Hi All,

I'm using a sumproduct formula with multiple conditions to count the number of rows. I'm having trouble with a time duration calculation.

At first I was trying to calulate the duration in the subproduct like this. But I get an error #value
=SUMPRODUCT(--('Query Data'!$B$2:$B$1000="NOC Incident"),--(('Query Data'!$G$2:$G$1000-'Query Data'!$F$2:$F$1000)>"00:10:10"))

Then I tried calculating the duration in a separate column and testing that column, but all I get is 0
=SUMPRODUCT(--('Query Data'!$B$2:$B$1000="NOC Incident"),--('Query Data'!$O$2:$O$1000>"00:59:59"))
I've formatted the field as time 37:30:50

I'm trying to count the rows where the duration is greater than 1 hour.

**Bob Phillips** · 04-05-2012, 12:31 PM

Try this

=SUMPRODUCT(--(($C$2:$C$1000-$B$2:$B$1000)>=--"01:00:00"))

and there is no need to array-enter SUMPRODUCT

**gav0101** · 04-05-2012, 02:34 PM

Originally Posted by Bob Phillips

Try this

=SUMPRODUCT(--(($C$2:$C$1000-$B$2:$B$1000)>=--"01:00:00"))

and there is no need to array-enter SUMPRODUCT

Thanks it works great.
My understanding was that you need array-enter with sumproduct, especially if you have multiple conditions.
Last what does the "--" after ">=" represent or mean?

**Bob Phillips** · 04-05-2012, 06:34 PM

Originally Posted by gav0101

My understanding was that you need array-enter with sumproduct, especially if you have multiple conditions.

That is a mis-understanding then, SUMPRODUCT intrinsically workss with arrays, so there is no need to array-enter it, with or without multiple conditions.

Originally Posted by gav0101

Last what does the "--" after ">=" represent or mean?

That is what makes my version work whereas yours didn't - see http://www.xldynamic.com/source/xld.SUMPRODUCT.html