Sumproduct if rank <=5 if not return 0

G

Guest

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
 
G

Guest

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

Guest

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Sumproduct - Return value as Blank 3
Rank Function Help Needed 7
Rank and return Names 5
Tricky Ranking Question 1
Rank based on 2 categories 6
VLOOKUP/MATCH Query - quite complex....but interesting. 3
Sumproduct Erro 1
(Advanced Excel) Figuring out a formula of continuous progressiontrying to create 19 Formulas into O 5

Top