Sumproduct using TIME format

ghard1

Active Member
Joined
May 4, 2005
Messages
331
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!
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Asala42

Well-known Member
Joined
Feb 26, 2002
Messages
2,318
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

Active Member
Joined
May 4, 2005
Messages
331
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
 

Asala42

Well-known Member
Joined
Feb 26, 2002
Messages
2,318

ADVERTISEMENT

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

Active Member
Joined
May 4, 2005
Messages
331
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

Active Member
Joined
May 4, 2005
Messages
331

ADVERTISEMENT

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

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

ghard1

Active Member
Joined
May 4, 2005
Messages
331
If I format the cells as numbers, the AV column is displayed as:

0.00069444444444447
 

Asala42

Well-known Member
Joined
Feb 26, 2002
Messages
2,318
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?)
 

Forum statistics

Threads
1,141,680
Messages
5,707,789
Members
421,527
Latest member
Tamiwsw

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top