asked on

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

Ejgil Hedegaard

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.

aikimark

Please post a sample workbook that includes data you need to count. Your posted data did not include any values greater than 25 hours

Tom Farrar

How are these being determined before doing the CountIf?

25 Hour*
26 Hour*
27 Hour*
28 Hour*
29 Hour*

SpyderGST1

ASKER

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

SpyderGST1

ASKER

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

SpyderGST1

ASKER

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

SpyderGST1

ASKER

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

ASKER CERTIFIED SOLUTION

Ejgil Hedegaard

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ejgil Hedegaard

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.

aikimark

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