variable range within sumproduct

[fabrecass]

I have the formula below, which calculates a percentage of information received on time - however, this needs to be a variable range, and if i state empty cells to anticipate additions it includes them when calculating the percentage? can anyone help me?

Please Login or Register  to view this content.

[Tony Valko]

I assume the values in columns L and M are numeric? (you can test TEXT for that same condition)

Is the data contiguous? In other words, are there any empty cells within the data range?

[fabrecass]

the values in L and M are all dates, so yes, numeric. and yes the data is contiguous.

[Tony Valko]

Create these dynamic named ranges...

Goto the menu Insert>Name>Define

Name: Rng1
Refers to:

=$L$2:INDEX($L$2:$L$1000,MATCH(1E100,$L$2:$L$1000))

Name: Rng2
Refers to:

=$M$2:INDEX($M$2:$M$1000,MATCH(1E100,$L$2:$L$1000))

Adjust for a reasonable end of range where I use down to row 1000.

Then, your formula becomes:

=SUMPRODUCT(--(Rng1>Rng2))/ROWS(Rng1)

If there are no entries in the ranges then the formula will return an #N/A error.

[fabrecass]

Thank you Tony - i shall test this now - will it work across 2 worksheets? so the formula on one sheet and the data on another.

[Tony Valko]

will it work across 2 worksheets? so the formula on one sheet and the data on another.

Yes. Just make sure to include the appropriate sheet names when creating the dynamic ranges.

[fabrecass]

thank you Tony, your solution worked perfectly.

[Tony Valko]

You're welcome. Thanks for the feedback!