How to perform a sumif using blank cells as a reference?

excel, excel-formula, vba

Solution

You can try this if you prefer using VBA:

Sub SmartRunningTotals()
    Dim rng As Range
    Dim cell As Range
    Dim lastRow As Long
    Dim totalTime As Double

    ' I'm assuming your time column is in column A
    lastRow = Range("A" & ActiveSheet.Rows.Count).End(xlUp).Row

    Set rng = Range("A2:A" & lastRow)

    For Each cell In rng
        totalTime = totalTime + cell.Value
        If cell.Offset(, 2).Value <> "" Then
            cell.Offset(, 3).Value = totalTime
            ' reset total after we write it to column D
            totalTime = 0
        End If
    Next

End Sub

Problem

Please see the example data below: ``` Time Date Result 00:21.6 10/1/2012 1:43 FALSE 01:47.7 10/1/2012 2:13 FALSE 00:56.7 10/1/2012 2:49 FALSE 00:54.9 10/1/2012 3:43 00:11.8 10/1/2012 3:43 02:10.9 10/1/2012 3:46 FALSE 01:05.4 10/1/2012 3:58 FALSE 00:55.8 10/1/2012 4:53 04:41.8 10/1/2012 4:52 00:26.3 10/1/2012 4:58 00:04.2 10/1/2012 4:58 00:15.3 10/1/2012 4:59 00:06.4 10/1/2012 4:57 00:10.7 10/1/2012 4:56 00:04.4 10/1/2012 4:56 00:04.2 10/1/2012 4:57 00:29.2 10/1/2012 4:57 00:34.5 10/1/2012 4:56 01:22.4 10/1/2012 4:55 00:08.1 10/1/2012 4:55 FALSE 03:20.9 10/1/2012 4:51 FALSE 00:56.3 10/1/2012 5:42 FALSE 02:23.1 10/1/2012 5:51 01:20.6 10/1/2012 5:48 00:09.8 10/1/2012 5:49 FALSE 01:40.0 10/1/2012 7:47 FALSE 01:13.4 10/1/2012 8:11 FALSE 00:41.6 10/1/2012 9:49 FALSE 01:08.1 10/1/2012 11:56 FALSE ``` I need to perform a certain type of calculation. If there is no blank cell in the result, I need the data in time cell next to the result. But if there is a blank cell in the result column, I need to perform a sum of the time in the rows that contain the blank cell and the first cell containing FALSE next to the blank cell. Please see the example output below: ``` 00:21.6 10/1/2012 1:43 FALSE 00:21.6 01:47.7 10/1/2012 2:13 FALSE 01:47.7 00:56.7 10/1/2012 2:49 FALSE 00:56.7 00:54.9 10/1/2012 3:43 00:11.8 10/1/2012 3:43 02:10.9 10/1/2012 3:46 FALSE 03:17.6(i.e., 00:54.9+00:11.8+02:10.9) 01:05.4 10/1/2012 3:58 FALSE 01:05.4 00:55.8 10/1/2012 4:53 04:41.8 10/1/2012 4:52 00:26.3 10/1/2012 4:58 00:04.2 10/1/2012 4:58 00:15.3 10/1/2012 4:59 00:06.4 10/1/2012 4:57 00:10.7 10/1/2012 4:56 00:04.4 10/1/2012 4:56 00:04.2 10/1/2012 4:57 00:29.2 10/1/2012 4:57 00:34.5 10/1/2012 4:56 01:22.4 10/1/2012 4:55 00:08.1 10/1/2012 4:55 FALSE 09:23.3(i.e., 00:55.8+04:41.8+00:26.3+00:04.2+00:15.3+00:06.4+00:10.7+00:04.4+00:04.2+00:29.2+00:34.5+01:22.4+00:08.1) 03:20.9 10/1/2012 4:51 FALSE 03:20.9 00:56.3 10/1/2012 5:42 FALSE 00:56.3 02:23.1 10/1/2012 5:51 01:20.6 10/1/2012 5:48 00:09.8 10/1/2012 5:49 FALSE 03:53.5(i.e., 02:23.1+01:20.6+00:09.8) 01:40.0 10/1/2012 7:47 FALSE 01:40.0 01:13.4 10/1/2012 8:11 FALSE 01:13.4 00:41.6 10/1/2012 9:49 FALSE 00:41.6 01:08.1 10/1/2012 11:56 FALSE 01:08.1 ``` If it is possible by any formulas or using visual basic editor please let me know. I am doing this manually right now. I have to handle about 10000-15000 rows per day. If you help me out I could save a lot of hours and use it to learn something new.

Original source