如何使用空白单元格作为引用来执行sumif ?

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.

我需要进行某种计算。如果结果中没有空白单元格，我需要结果旁边的时间单元格中的数据。但是，如果结果列中有一个空单元格，我需要在包含空单元格和包含空单元格旁边包含FALSE的第一个单元格的行中执行时间的总和。

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.

如果可以通过任何公式或使用visual basic编辑器，请让我知道。我现在正在手动操作。我每天要处理大约10000-15000行。如果你能帮我，我就能省下很多时间来学习新的东西。

4 个解决方案

#1

You can try this if you prefer using VBA:

如果你喜欢使用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

#2

Pretty easy in two columns. Calculate a running total and reset if the cell above is not blank.

两列很简单。计算运行总数，如果上面的单元格不是空的，则重置。

E.g.

如。

(Time = Column H, Date = Column I, Result = Column J )

(时间=列H，日期=列I，结果=列J)

Column N
    =IF(J1="",H2+N1,H2)
    =IF(J2="",H3+N2,H3)
    =IF(J3="",H4+N3,H4)
    =IF(J4="",H5+N4,H5)
    =IF(J5="",H6+N5,H6)

Column O

    =IF(J2="","",O2)
    =IF(J3="","",O3)
    =IF(J4="","",O4)
    =IF(J5="","",O5)
    =IF(J6="","",O6)

Copy those formulas on down

把公式抄下来

#3

Assuming data starts at row 2 with headers in row 1 then you can use this formula in D2 copied down

假设数据从第2行开始，在第1行中有头，那么你可以使用D2复制下来的这个公式。

=IF(C2<>"",SUM(A$2:A2)-SUM(D$1:D1),"")

=如果(C2 < > ",金额(美元2:A2)和(D 1美元:D1)" ")

#4

I'm assuming your existing range starts in cell A1 (i.e. the word "Time" exists in cell A1). I am also assuming that in column C, the two possible values are the logical value FALSE (not the text string "FALSE"), or blank (not a string of 1 or more spaces).

假设现有的范围从A1单元格开始(即A1单元格中存在“时间”一词)。我还假设在C列中，两个可能的值是逻辑值FALSE(不是文本字符串“FALSE”)，或者是空白(不是1个或多个空格的字符串)。

In cell D1, enter the formula =IF(AND(C2=FALSE,C1=""),D1+1,1)

在单元D1中，输入公式=IF(和(C2=FALSE,C1=")，D1+1,1)

In cell E1, enter the formula =IF(C2="","",SUM(OFFSET(A2,-(D2-1),0,D2,1)))

在细胞E1,输入公式=如果(C2 = " "," ",总和(抵消(A2 -(D2-1),0,D2,1)))

Set the number format in cell E1 to "mm:ss.0"

将单元格E1中的数字格式设置为“mm:ss.0”

Copy D1 and E1 all the way down your range. Hide column D if desired.

复制D1和E1到你的范围。如果需要，隐藏D列。

#1