# Sumproduct using TIME format

#### ghard1

##### Active Member
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
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
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.

#### ghard1

##### Active Member
Is it possible I am formatting the cells wrong?

#### Asala42

##### Well-known Member

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
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

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
If I format the cells as numbers, the AV column is displayed as:

0.00069444444444447

#### Asala42

##### Well-known Member
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?)

Replies
12
Views
531
Replies
2
Views
119
Replies
5
Views
203
Replies
8
Views
124
Replies
4
Views
322

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.

### Which adblocker are you using?

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

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