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
SpyderGST1Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

Ejgil HedegaardCommented:
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)

Open in new window

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.
aikimarkCommented:
Please post a sample workbook that includes data you need to count.  Your posted data did not include any values greater than 25 hours
tomfarrarCommented:
How are these being determined before doing the CountIf?

25 Hour*
26 Hour*
27 Hour*
28 Hour*
29 Hour*
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

SpyderGST1Author 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.
SpyderGST1Author 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.
SpyderGST1Author 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.
SpyderGST1Author Commented:
Please note,
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
Ejgil HedegaardCommented:
Use this instead
=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

Your issues matter to us.

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

Start your 7-day free trial
Ejgil HedegaardCommented:
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.
aikimarkCommented:
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

Open in new window


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

Open in new window


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

Open in new window

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.