Sum From Variable Range (2000)

Topic

New Reply

Hello all,

I am working on a quality reporting project and I am trying to determine the total end-to-end processing time for all manual transactions, i.e. from the time they entered our system to the time they posted and left our system.

The basic structure of the spreadsheet is (I’ve attached a sample):

Column A: TRN

Reply | Quote

Replies

Legare’s formula is fantastic but I think it needs to have some absolutes used or you may run into a few problems.

=IF(E2=”FEDIN1″,MAX(($A$2:$A$26=A2)*$G$2:$G$26)-G2,””)

just an observation.

yoyo

Reply | Quote

You are correct, it would have been better to make those ranges absolute. It actually should work just fine without the absolutes addresses, but if the worksheet layout were changed, it could break. Thanks for the improvement.

Reply | Quote

A big thanks to both Legare and yoyo – I tried the formula this morning and it worked perfectly!

Scott

Reply | Quote

A big thanks to both Legare and yoyo – I tried the formula this morning and it worked perfectly!

Scott

Reply | Quote

You are correct, it would have been better to make those ranges absolute. It actually should work just fine without the absolutes addresses, but if the worksheet layout were changed, it could break. Thanks for the improvement.

Reply | Quote

Legare’s formula is fantastic but I think it needs to have some absolutes used or you may run into a few problems.

=IF(E2=”FEDIN1″,MAX(($A$2:$A$26=A2)*$G$2:$G$26)-G2,””)

just an observation.

yoyo

Reply | Quote

Put the formula below into cell L2. It is an array formula, so you must hold down Shift+Ctrl when you press Enter to enter the formula into the cell. Then copy the formula down the column.

=IF(E2="FEDIN1",MAX((A2:A26=A2)*G2:G26)-G2,"")

You will need to adjust the end of the ranges to fit your actual worksheet.

Reply | Quote

Put the formula below into cell L2. It is an array formula, so you must hold down Shift+Ctrl when you press Enter to enter the formula into the cell. Then copy the formula down the column.

=IF(E2="FEDIN1",MAX((A2:A26=A2)*G2:G26)-G2,"")

You will need to adjust the end of the ranges to fit your actual worksheet.

Reply | Quote