# Need a formula

This the current formula:
=SUM(COUNTIF(Current!E2:E999000,"25 hour*"),COUNTIF(Current!E2:E999000,"26 hour*"),COUNTIF(Current!E2:E999000,"27 hour*"),COUNTIF(Current!E2:E999000,"28 hour*"),COUNTIF(Current!E2:E999000,"29 hour*"))

I don’t want to look for each “hour set”
I want to simplify it and look for anything greater than 25 hours.
I want to do something like this but its not working.

=SUM(COUNTIF(Current!E:E,">=25 hour*"))

This is a sample data in the column the formula is querying:
36 seconds
1 minute 13 seconds
31 seconds
58 minutes 32 seconds
29 minutes 53 seconds
6 hours 28 minutes
4 minutes 51 seconds
2 minutes 25 seconds
1 hour 16 minutes
17 minutes 34 seconds
37 minutes 20 seconds
33 minutes 43 seconds
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
Try this
``````=SUMPRODUCT((LEFT(Current!E:E,2)>=25)*(MID(Current!E:E,4,5)="hours"))+SUMPRODUCT((MID(Current!E:E,5,5)="hours")*1)
``````
It works up to 999 hours.
Delete last part if only up to 99 hours

Restrict the ranges if possible.
It is much faster than calculating for 1 million rows.
Commented:
Please post a sample workbook that includes data you need to count.  Your posted data did not include any values greater than 25 hours
Commented:
How are these being determined before doing the CountIf?

25 Hour*
26 Hour*
27 Hour*
28 Hour*
29 Hour*
Author Commented:
@Ejgil - I tried your formula. It doesn't count accurately. I have no cells with entries greater than 25 hours and it counted 22.

I'll attach the file.
Author Commented:
@aikimark,
I know there was no value over 25 hours. I want to know if there is an entry over 25 hours. I'll attach the file.
Author Commented:
@tomfarrar,
These values are generated by a reporting tool and saved as a csv file. The csv file is imported into the excel workbook. These values exist in the csv file before they are imported.
Author Commented:
I had to change the values in column a and b for sensitivity purposes.
I am trying to simplify the formula in worksheet "dashboard" cell B25
BnS-Metrics-20151023.xlsx
Commented:
=SUMPRODUCT((VALUE(LEFT(Current!\$E\$2:\$E\$36398,2))>=25)*(MID(Current!\$E\$2:\$E\$36398,4,5)="hours"))

The value function fail if the cell is empty, so the range must be the real used data range.
It is a text import, so Excel will automatically adjust the range in the formula when the data is updated.

You should always use the real data range (row 2 to bottom of the range) when addressing text imports or query tables.
It calculate faster not using the entire columns, and Excel adjust the range in the formulas when data are updated, so no need to specify more.

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Commented:
Just seen your directions at the top of the sheet.
You specify to delete the data on the Current tab, import to another tab, and then copy that.
No need for that.
Just right click somewhere in the data on the Current tab, and select update.
The data specification is set to ask for the file name, so select the new file and import that.
It will replace the data and adjust the ranges.
Commented:
If you add this function to a module in your workbook (needs to be xlsm file extension to do this)
``````Function TextTime2Time(parmText)
Static oRE As Object
Dim oMatches As Object
Dim oSM As Object
If oRE Is Nothing Then
Set oRE = CreateObject("vbscript.regexp")
oRE.Pattern = "(?:(\d{1,2}) hours?){0,1}\s*(?:(\d{1,2}) minutes?){0,1}\s*(?:(\d{1,2}) seconds?){0,1}"
End If
If oRE.test(parmText) Then
Set oMatches = oRE.Execute(parmText)
Set oSM = oMatches(0).submatches
TextTime2Time = CDate(Right("0" & oSM(0), 2) & ":" & Right("0" & oSM(1), 2) & ":" & Right("0" & oSM(2), 2))
Else
TextTime2Time = vbNullString
End If
End Function
``````

Then use this formula in cells to the right of the duration time text and fill down.
``````=TextTime2Time(E2)*24
``````

Your interval counts are available with this series of formulas.  These formulas were in cells below the newly added formulas, one below the other.
``````=COUNTIF(\$G\$2:\$G\$34232,"<1")
=COUNTIF(\$G\$2:\$G\$34232,"<3")-SUM(G\$34234:G34234)
=COUNTIF(\$G\$2:\$G\$34232,"<5")-SUM(G\$34234:G34235)
=COUNTIF(\$G\$2:\$G\$34232,"<7")-SUM(G\$34234:G34236)
=COUNTIF(\$G\$2:\$G\$34232,"<9")-SUM(G\$34234:G34237)
=COUNTIF(\$G\$2:\$G\$34232,"<11")-SUM(G\$34234:G34238)
=COUNTIF(\$G\$2:\$G\$34232,"<13")-SUM(G\$34234:G34239)
=COUNTIF(\$G\$2:\$G\$34232,"<15")-SUM(G\$34234:G34240)
=COUNTIF(\$G\$2:\$G\$34232,"<17")-SUM(G\$34234:G34241)
=COUNTIF(\$G\$2:\$G\$34232,"<19")-SUM(G\$34234:G34242)
=COUNTIF(\$G\$2:\$G\$34232,"<21")-SUM(G\$34234:G34243)
=COUNTIF(\$G\$2:\$G\$34232,"<23")-SUM(G\$34234:G34244)
=COUNTIF(\$G\$2:\$G\$34232,"<25")-SUM(G\$34234:G34245)
=COUNTIF(\$G\$2:\$G\$34232,">25")
``````
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.