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!

[TABLE="width: 200"]
<tbody>[TR]
[TD="align: center"]Number[/TD]
[TD="align: center"]Time[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]100001[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]1200000[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]86989[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]86007[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]103011[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]72996[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]101014[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]91998[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]1400000[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]127005[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"]73994[/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: center"]2000000000[/TD]
[/TR]
</tbody><colgroup><col><col></colgroup>[/TABLE]

 

[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