Converting Sumproduct Formulas into Time

[lukerees83]

I have a formula that I am currently using to make caculation on cells that are in the "general" format in Excel 2003.

It is working on 2 columns of data and is designed to return a value if the figure in column is >0 and <=30 AND if the value in column P is greater than zero.

=SUMPRODUCT(--($O$2:$O$1300>0),--($O$2:$O$1300<=30),--($P$2:$P$1300>0))

I now have to apply the formula in exactly the same way but this time to cells that are in the hh:mm time format and have no idea how. I didn't write the original formula so am a bit sketchy on how it works.

But in a nutshell I now need the formula to count when the value in column S is greater than 00:00 and less than or equal to 00:30 AND the value in T is greater than 00:00

Hope this makes sense. Any ideas?

 

[VoG]

Try

=SUMPRODUCT(--(S2:S300>"00:00"+0),--(S2:S300<="00:30"+0),--(T2:T300>"00:00"+0))

 

[lukerees83]

Brilliant. This overjoys me!

Many thanks

L

 

[T. Valko]

I have a formula that I am currently using to make caculation on cells that are in the "general" format in Excel 2003.

It is working on 2 columns of data and is designed to return a value if the figure in column is >0 and <=30 AND if the value in column P is greater than zero.

=SUMPRODUCT(--($O$2:$O$1300>0),--($O$2:$O$1300<=30),--($P$2:$P$1300>0))

I now have to apply the formula in exactly the same way but this time to cells that are in the hh:mm time format and have no idea how. I didn't write the original formula so am a bit sketchy on how it works.

But in a nutshell I now need the formula to count when the value in column S is greater than 00:00 and less than or equal to 00:30 AND the value in T is greater than 00:00

Hope this makes sense. Any ideas?

Use cells to hold the criteria:

• A2 = 0:00
• B2 = 0:30

Then:

=SUMPRODUCT(--($S$2:$S$1300>A2),--($S$2:$S$1300<=B2),--($T$2:$T$1300>A2))