Sumproduct if rank <=5 if not return 0

T

tmirelle

here is the equation I have

=SUMPRODUCT(--('06.07'!$B$1:$B$1000='06.07Analysis'!B$1),--('06.07'!$F$1:$F$1000='06.07Analysis'!$A14))

Now what I would like to do is:

If the above function generates a result that is in the Top 5 of a range
(i.e. ranks between 1 & 5) then display the result to the equation above. If
not then return 0
 
T

T. Valko

Try this:

=IF(SUMPRODUCT(--('06.07'!$B$1:$B$1000='06.07Analysis'!B$1),--('06.07'!$F$1:$F$1000='06.07Analysis'!$A14))<=5,SUMPRODUCT(--('06.07'!$B$1:$B$1000='06.07Analysis'!B$1),--('06.07'!$F$1:$F$1000='06.07Analysis'!$A14)),0)

Biff
 
B

bj

what range?
As the equation sits it is a single value. are there other sumproduct
equations you are comparing against?
 
T

tmirelle

range is '06.07Analysis'!B$1:B$21

bj said:
what range?
As the equation sits it is a single value. are there other sumproduct
equations you are comparing against?
Click to expand...
 
Top