Reference all numerical values in a column EXCEPT for last value

[nc_waggoner]

I have a sheet with a counter in column D and associated times in column E as numerical milliseconds (ex. 72996 or 72.9 seconds). I am trying to figure out a way to sum the values above a certain limit (900,000) on all rows with a time with the exception of the last row which always provides bad data (due to the program). In the example below, I need to sum blocks 2 and 9 since they are above 900,000 but NOT block 12 since it is the last row. Please help!

Number	Time
1	100001
2	1200000
3	86989
4	86007
5	103011
6	72996
7	101014
8	91998
9	1400000
10	127005
11	73994
12	2000000000

<tbody>
</tbody><colgroup><col><col></colgroup>

 

[Eric W]

If there are no gaps in the range, and the cell after the last value is empty, then maybe:

=SUMPRODUCT(B2:B13,--(B2:B13>D2),--(B3:B14<>""))

where D2 has 900,000 and B2:B13 is your range. Note the 1-row offset in the last range.

 

[igold]

How about this:

=SUMIF(E2:E13,">900000")-SUMIF(D2:D13,MAX(D2:D13),E2:E13)


igold

 

[nc_waggoner]

Eric,

The rows after are not blank. It is either a "-" or N/A. This is just how the data is pulled in from Historian

 

[nc_waggoner]

Also I should mentioned that the information is dynamic and the last row value changes daily...could be row 900 and then 875 the next day

 

[nc_waggoner]

Additionally, sorry for not putting this on one ... the "Number" column cannot be referenced unless you copy and paste as a value. The data comes out in a format that Excel does not like/recognize...the "time" column seems to be work with formulas

 

[igold]

For dynamic last row...

=SUMIF(E:E,">900000")-SUMIF(D:D,MAX(D:D),E:E)


igold

 

[igold]

If excel cannot read column D then how about this:

=SUMIF(E:E,">900000")-INDIRECT("E"&COUNTA(E:E))

igold