Sumproduct using TIME format

[ghard1]

Hello everyone.

I am having a problem with the following SUMPRODUCT formula:

=SUMPRODUCT((Data!$AV$2:$AV$5000>F72)*(Data!$AV$2:$AV$5000<G72)

Where:

Data! AV2:AV5000 = a time (calculated by a formula) and formatted in [mm]:ss

F72 = 0:00 and the cell is formatted as [mm]:ss

G72 = 02:00 and the cell is formatted as [mm]:ss

I am trying to calculate how often the answer displayed in data!av2:av5000 is greater than zero, but less than 2 minutes.

As of now, the formula above is returning all instances of data!av2:av5000 being greater than zero only.

Any help would be greatly appreciated as always.

Thanks!

 

[Asala42]

Data! AV2:AV5000 = a time (calculated by a formula) and formatted in [mm]:ss
F72 = 0:00 and the cell is formatted as [mm]:ss
G72 = 02:00 and the cell is formatted as [mm]:ss

As a test, what happens if you format F72, G72, and an entry in AV as a number? I'm wondering if Excel is looking at one or more of these cells as actually a Date+Time

 

[ghard1]

If I format all as numbers, the formula returns the same result. The formula I have in Data!AV is:
=IF(AND(COUNT(G2,H2)=2,H2-G2=0),TIMEVALUE("0:0:10"),IF(COUNT(G2,H2),H2-G2,""))

This may be the problem. I am using the above formula because I would like to subtract the numbers in Column G from the numbers in Column H as long as there is a number in each. ALSO, if these two numbers subtracted equal 0 ((zero) than I would like to formula result to be 10 seconds.

Also, I am subtracting two "time of day" (Column G,H) but want the result to be time as in minutes in seconds, not time of day. If I copy and paste the result in AV (value and number format only) and look at the formula bar, it displays the result in the following format:

12:00:10 AM

Instead of displaying 00:10 for 0 minutes and 10 seconds.

Thanks for your help

 

[ghard1]

Is it possible I am formatting the cells wrong?

 

[Asala42]

The test I was going for was to see the numbers behind the format in some of your data.

2 minutes in a number format is this decimal: 0.0013888889

IF your data in number format turns out to be this: 39007.0013888889 then that means it actually has the value of today+2 minutes (which is substantially larger than 2 minutes).

Also, I think you're sumproduct formula was truncated in the original post. Can you repost it?

 

[ghard1]

Not sure why it keeps getting truncated.

=SUMPRODUCT((Data!$AV$2:$AV$5000>F123)*(Data!$AV$2:$AV$5000 < H123))

I have added spaces before and after the greater than sign because for some reason if they aren't there, the formula is truncated.

 

[ghard1]

F123 = 0:00 and the cell is formatted as [mm]:ss

H123 = 02:00 and the cell is formatted as [mm]:ss

 

[ghard1]

If I format the cells as numbers, the AV column is displayed as:

0.00069444444444447

 

[Asala42]

Well you're formula looks ok. It works fine when I duplicate what you're describing on a test worksheet on my system.

If you format F123 and H123 as a number - what values do you get?

(In theory it should be 0 and 0.0013888888889, correct?)