Sumproduct if rank <=5 if not return 0
 

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

Sumproduct if rank <=5 if not return 0
 

Try this:

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

Biff

"tmirelle" wrote in message
...
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

Sumproduct if rank <=5 if not return 0
 

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

"tmirelle" wrote:

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

Sumproduct if rank <=5 if not return 0
 

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

"bj" wrote:

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

"tmirelle" wrote:

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