Microsoft Excel 2003 (full product)

i am trying to rank 4 areas. Example:

Div =cell B4_

Reg =cell E4_

Mkt =cell H4_

St =cell K4_I want to input a number in one of the fields above and then I want to pull back the rank.

Here is the formula i am using. Please help me create a ranking formula for the 4 cells above.

Hope this makes sense.

=IF($B$4>0,SUMIF(RawData!$A$4:$A$4500,Report!$B$4,RawData!$E$4:$E$4500),

IF($E$4>0,SUMIF(RawData!$B$4:$B$4500,Report!$E$4,RawData!$E$4:$E$4500),

IF($H$4>0,SUMIF(RawData!$C$4:$C$4500,Report!$H$4,RawData!$E$4:$E$4500),

IF($K$4>0,SUMIF(RawData!$D$4:$D$4500,Report!$K$4,RawData!$E$4:$E$4500),0))))Edit: Post edited by Office moderator. Formula split into multiple lines for ease of viewing.

Hi, I am not sure what you are trying to rank.

You say that you want to rank 4 areas, and then you say you want to put a value in One of the four cells.

If all four cells are zero, your formula returns zero.

If K4 is greater than zero, and the other three are zero it returns the result of the conditional sum for E4:E4500 on the raw data sheet, where cells in A4:A4500 = K4 on sheet Report.The formula will only return

onevalue, based on the first of the four cells B4, E4, H4, K4 that is greater than zero.From this am I right in thinking that cells B4, E4, H4, K4 are on the Report worksheet and that your formula is also on the Report worksheet.

This brings me back to the question what are you trying to rank?

The formula only returns a single value which is the sum of all cells in the range E4:E4500, based on a comparison using columns A, B, C or D against the value in the first of the four cells B4, E4, H4, K4 which is greater than zero.

If all four of the cells B4, E4, H4, K4 contained a value and you had the results of four conditional sums, did you want to rank the result of the four conditional sums?

For example

if SUMIF(RawData!$A$4:$A$4500,Report!$B$4,RawData!$E$4:$E$4500), was 100,

and SUMIF(RawData!$B$4:$B$4500,Report!$E$4,RawData!$E$4:$E$4500), was 20,

and SUMIF(RawData!$C$4:$C$4500,Report!$H$4,RawData!$E$4:$E$4500), was 300,

and SUMIF(RawData!$D$4:$D$4500,Report!$K$4,RawData!$E$4:$E$4500), was 40The rank positions of B4, E4, H4, K4 are 2, 4, 1, 3.

Can you provide additional information.

Regards

You are correct in your thinking. I will only enter a number in 1 of these feilds at a time which will bring back a value based on the formula below. I am trying to bring back a rank at the same time when you enter cell B4. Example:

Div =cell B4_ Div = _rankB7_

Reg =cell E4_ Etc......

Mkt =cell H4_

St =cell K4_How does that ranking formula need to be.

=IF($B$4>0,SUMIF(RawData!$A$4:$A$4500,Report!$B$4,RawData!$E$4:$E$4500),

IF($E$4>0,SUMIF(RawData!$B$4:$B$4500,Report!$E$4,RawData!$E$4:$E$4500),

IF($H$4>0,SUMIF(RawData!$C$4:$C$4500,Report!$H$4,RawData!$E$4:$E$4500),

IF($K$4>0,SUMIF(RawData!$D$4:$D$4500,Report!$K$4,RawData!$E$4:$E$4500),0))))

Hi, Sorry, still don't get it.

What are you trying to rank.

Your formula has nothing to do with ranking. It just returns a single value which is the sum (total) of all cells in E4:E4500 in rows where there is a match in column A, B, C or D, depending on the first of the four cells B4, E4, H4 or K4 that is greater than zero.

Can you give an example of the data and which cells are to be ranked.

Also can you explain what Div, Reg, Mkt, St are. Are they named variables or named ranges or just text headings.

Regards

Div. is just a text heading. I am ranking raw data numbers. The formula brings back the number i need. Here is an example: i have a box labeled Div. (division), when i put the letter (A) in the cell, it sums the raw data from another sheet to bring back the value for all of (A). I would like it to rank the letter (A). The reason i need to rank the letter a is because i have 23 different divisions with different letters.

re: I am trying to bring back a rank at the same time when you enter cell B4As Humar has said, it is not clear what you are trying to rank.

What does this mean?

Div =cell B4_ Div = _rankB7_Do the underscores ( _ ) have any significance?

Hi, If you put A in the cell next to Div. you have a formula that sums all the values that have A in a certain column.

Entering A returns a single value

Entering B returns a different value - the sum of all the values that have B in a certain columnAs your approach only returns single values, you can't get a rank.

What I suggest you do is have the ranking created on the raw data sheet.

If columns X, Y and Z are available, put all division names in column X (X2 to X23) in column Y,(Y2 to Y23) in Y2 put this formula:SUMIF($A$4:$A$4500,X2,$E$4:$E$4500)

In column Z (Z2:Z23) in cell Z2 enter=RANK(Y2,$Y$2:$Y$23)etc.

Just drag the formulas down to extend them to row 23.You now have a table of subtotals for each division and their ranks.

On the summary sheet use VLOOKUP() in two cells to return the subtotal for the division and its rankSomething like this :

B C 3 Divison name A 4 Subtotal 100 5 Rank 21

In cell C4 enter this formula: =VLOOKUP(C$3,RawData!$X$2:$Z$23,2,FALSE)

in cell C5 enter this formula: =VLOOKUP(C$3,RawData!$X$2:$Z$23,3,FALSE)In C3 the VLOOKUP() looks for the value in cell C3 (A in this example), in column X on the raw data sheet and returns the result on the same row in the second column, which is column Y.

The next VLOOKUP() returns the value from the third column, which is column Z.Regards

I got it. THanks 1 more question. This is my formula.

=RANK(VLOOKUP(Report!$B$4,RawData!$R$4:$S$15,2,0),RawData!$S$4:$S$15)

#n/a shows up in the cell. How do i have that not showing?

Hi, The #NA is generated by the VLOOKUP() function when it does not find a matching value.

You can wrap the VLOOKUP() function in a test:

=IF(ISNA(VLOOKUP(Report!$B$4,RawData!$R$4:$S$15,2,0)),"",

RANK(VLOOKUP(Report!$B$4,RawData!$R$4:$S$15,2,0),RawData!$S$4:$S$15))

I have split the formula onto two lines for ease of viewing.If the VLOOKUP() returns the NA error, 'IF(ISNA(' tests for it and returns ""

If it does not return NA, the original formula is used.Regards

Actually, i got that now too. THanks for your assistance.

You're welcome Regards

Humar

Ask Your Question

Weekly Poll

Would you use "Spotlight Search" on Windows?

Discuss in The Lounge

Poll History