Using RANK and SUMIF together?

[flouee]

Hello!

I am looking for a formula to select specific rows in a data sheet (according to a set of criterias), calculate the rank (RANK) for some of these rows (a new set of criterias), and then summarize those ranks. Is this possible and how would I do it? I was thinking about combining RANK with SUMIF but I have not been able to get it to work...

An example; I want to select all rows where column B="5", and then summarize the ranks of rows where B="5" and C="10".

(Each row of data can be in different "rank groups", so it is not possible to add an extra column for each row and calculate the rank directly in the data sheet; the rank will depend on which other rows are included in each set.)

If it helps, I need the ranks for the rows in order to do the Mann-Whitney test.

 

[Jerry Sullivan]

Hello and Welcome,

What you are describing can probably be done using array formulas. The specifics of how to do that will be dependent on how your data is organized.

Please post an example representing the columns and 2 small "groups". You can post a snapshot using MrExcel's HTML Maker or Excel Jeanie.

 

[flouee]

Ok, here we go:

Data

<table style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:69px;"><col style="width:62px;"><col style="width:62px;"><col style="width:55px;"><col style="width:50px;"><col style="width:46px;"><col style="width:71px;"><col style="width:60px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td> </td><td>H</td><td>I</td><td>J</td><td>K</td><td>L</td><td>M</td><td>N</td><td>O</td></tr><tr style="height:38px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">1</td><td style="font-weight:bold; font-family:Garamond; font-size:12pt; ">Mouse</td><td style="font-weight:bold; font-family:Garamond; font-size:12pt; text-align:right; ">Tot IgG mgG-2</td><td style="font-weight:bold; font-family:Garamond; font-size:12pt; text-align:right; ">IgG1 mG-2</td><td style="font-weight:bold; font-family:Garamond; font-size:12pt; ">IgG2c mgG-2</td><td style="font-weight:bold; font-family:Garamond; font-size:12pt; ">IFN-? </td><td style="font-weight:bold; font-family:Garamond; font-size:12pt; ">Plack assay</td><td style="font-weight:bold; font-family:Garamond; font-size:12pt; ">PCR</td><td style="font-weight:bold; font-family:Garamond; font-size:12pt; ">End score</td></tr><tr style="height:19px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">145</td><td style="font-family:Garamond; font-size:12pt; text-align:left; ">Mouse 2</td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">7290</td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">7290</td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">7290</td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">1</td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">450</td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">845000</td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">0</td></tr><tr style="height:19px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">146</td><td style="font-family:Garamond; font-size:12pt; text-align:left; ">Mouse 1</td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">21870</td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">7290</td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">65610</td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">9</td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">100</td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">216000</td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">0</td></tr><tr style="height:19px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">155</td><td style="font-family:Garamond; font-size:12pt; text-align:left; ">Mouse 3</td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">7290</td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">30</td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">7290</td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">243</td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">290</td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">2070000</td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">4</td></tr><tr style="height:19px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">171</td><td style="font-family:Garamond; font-size:12pt; text-align:left; ">Mouse 3</td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">65610</td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">0</td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">65610</td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">6561</td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">10</td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">80000</td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">0</td></tr><tr style="height:19px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">175</td><td style="font-family:Garamond; font-size:12pt; text-align:left; ">Mouse 1</td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">7290</td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">0</td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">810</td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">81</td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">170</td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">1500000</td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">4</td></tr><tr style="height:19px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">176</td><td style="font-family:Garamond; font-size:12pt; text-align:left; ">Mouse 2</td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">72900</td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">2700</td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">900</td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">0,333</td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">30</td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">460000</td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">4</td></tr><tr style="height:19px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">192</td><td style="font-family:Garamond; font-size:12pt; text-align:left; ">Mouse 3</td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">2430</td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">270</td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">810</td><td style="font-family:Garamond; font-size:12pt; "> </td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">40</td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">1090000</td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">0</td></tr><tr style="height:19px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">194</td><td style="font-family:Garamond; font-size:12pt; text-align:left; ">Mouse 1</td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">21870</td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">2430</td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">7290</td><td style="font-family:Garamond; font-size:12pt; "> </td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">150</td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">660000</td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">0</td></tr><tr style="height:19px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">197</td><td style="font-family:Garamond; font-size:12pt; text-align:left; ">Mouse 2</td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">65610</td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">0</td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">65610</td><td style="font-family:Garamond; font-size:12pt; "> </td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">70</td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">1140000</td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">0</td></tr><tr style="height:19px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">205</td><td style="font-family:Garamond; font-size:12pt; text-align:left; ">Mouse 1</td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">810</td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">810</td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">90</td><td style="font-family:Garamond; font-size:12pt; "> </td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">320</td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">260000</td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">0</td></tr><tr style="height:19px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">206</td><td style="font-family:Garamond; font-size:12pt; text-align:left; ">Mouse 3</td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">270</td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">90</td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">0</td><td style="font-family:Garamond; font-size:12pt; "> </td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">380</td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">690000</td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">0</td></tr><tr style="height:19px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">209</td><td style="font-family:Garamond; font-size:12pt; text-align:left; ">Mouse 2</td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">810</td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">90</td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">270</td><td style="font-family:Garamond; font-size:12pt; "> </td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">280</td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">260000</td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">0</td></tr><tr style="height:19px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">211</td><td style="font-family:Garamond; font-size:12pt; text-align:left; ">Mouse 1</td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">21870</td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">30</td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">2430</td><td style="font-family:Garamond; font-size:12pt; "> </td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">220</td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">230000</td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">0</td></tr><tr style="height:19px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">212</td><td style="font-family:Garamond; font-size:12pt; text-align:left; ">Mouse 3</td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">21870</td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">0</td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">2430</td><td style="font-family:Garamond; font-size:12pt; "> </td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">260</td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">370000</td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">0</td></tr><tr style="height:19px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">227</td><td style="font-family:Garamond; font-size:12pt; text-align:left; ">Mouse 2</td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">270</td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">90</td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">0</td><td style="font-family:Garamond; font-size:12pt; "> </td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">1040</td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">440000</td><td style="font-family:Garamond; font-size:12pt; text-align:right; ">4</td></tr></tbody></table>
These mice should be divided into two groups; those with end score =0 and those with end score=4. I want to calculate the sum of the ranks of each group's Tot IgG mgG-2. For example; I need to select the mice with end score = 0, calculate the rank for each of those mice and then summarize it. How can I do that?

 

[Jerry Sullivan]

These mice should be divided into two groups; those with end score =0 and those with end score=4. I want to calculate the sum of the ranks of each group's Tot IgG mgG-2. For example; I need to select the mice with end score = 0, calculate the rank for each of those mice and then summarize it. How can I do that?

I'm understanding the part about getting the rank of each item within its group.

For example, for the Group of "End Score = 4", there are 4 items matching this criteria on Rows 155,175,176 and 227. Since their Tot IgG mgG-2 values are (7290, 7290, 72900, 270) respectively, their ranks would be:
Simple Ranking: (2,2,1,4)
Unique Ranking: (2,3,1,4)

Once we have that for each item, then what is your desired result output?

I'm not clear on what you mean by "calculate the sum of the ranks of each group's Tot IgG mgG-2".

You probably don't mean additive sum (2+3+1+4) = 10 ...that wouldn't be very useful.

Do you want a column that has these ranking values on each corresponding row (recognizing that the rows for the group are not continguous)?

Or do you want a separate table for each group listing one or more fields from each row in ascending order of their ranking?

Sorry, I'm a little slow to follow on this.

 

[flouee]

Thanks for helping!

No, I want their ranks when compared to also mice with end score =0. Perhaps a better example:

Blad2

<table style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:59px;"><col style="width:112px;"><col style="width:84px;"><col style="width:75px;"><col style="width:56px;"><col style="width:66px;"><col style="width:64px;"><col style="width:256px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td> </td><td>A</td><td>B</td><td>C</td><td>D</td><td>E</td><td>F</td><td>G</td><td>H</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">1</td><td>Mouse</td><td>Tot IgG mgG-2</td><td>IFN-? </td><td>Plack assay</td><td>PCR</td><td>End score</td><td>Rank</td><td> </td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">2</td><td>Mouse 2</td><td style="text-align:right; ">7290</td><td style="text-align:right; ">1</td><td style="text-align:right; ">450</td><td style="text-align:right; ">845000</td><td style="text-align:right; ">0</td><td style="text-align:right; ">5</td><td>Formula for rank: =RANK(B2;$B$2:$B$9)</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">3</td><td>Mouse 1</td><td style="text-align:right; ">21870</td><td style="text-align:right; ">9</td><td style="text-align:right; ">100</td><td style="text-align:right; ">216000</td><td style="text-align:right; ">0</td><td style="text-align:right; ">3</td><td> </td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">4</td><td>Mouse 3</td><td style="text-align:right; ">7290</td><td style="text-align:right; ">243</td><td style="text-align:right; ">290</td><td style="text-align:right; ">2070000</td><td style="text-align:right; ">4</td><td style="text-align:right; ">5</td><td> </td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">5</td><td>Mouse 3</td><td style="text-align:right; ">65610</td><td style="text-align:right; ">6561</td><td style="text-align:right; ">10</td><td style="text-align:right; ">80000</td><td style="text-align:right; ">0</td><td style="text-align:right; ">2</td><td> </td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">6</td><td>Mouse 1</td><td style="text-align:right; ">7290</td><td style="text-align:right; ">81</td><td style="text-align:right; ">170</td><td style="text-align:right; ">1500000</td><td style="text-align:right; ">4</td><td style="text-align:right; ">5</td><td> </td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">7</td><td>Mouse 2</td><td style="text-align:right; ">72900</td><td style="text-align:right; ">0,333333333</td><td style="text-align:right; ">30</td><td style="text-align:right; ">460000</td><td style="text-align:right; ">4</td><td style="text-align:right; ">1</td><td> </td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">8</td><td>Mouse 3</td><td style="text-align:right; ">2430</td><td> </td><td style="text-align:right; ">40</td><td style="text-align:right; ">1090000</td><td style="text-align:right; ">0</td><td style="text-align:right; ">8</td><td> </td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">9</td><td>Mouse 1</td><td style="text-align:right; ">21870</td><td> </td><td style="text-align:right; ">150</td><td style="text-align:right; ">660000</td><td style="text-align:right; ">0</td><td style="text-align:right; ">3</td><td> </td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">10</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">11</td><td> </td><td>Sum of ranks for end score = 0:</td><td> </td><td> </td><td style="font-weight:bold; text-align:right; ">21</td><td> </td><td> </td><td> </td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">12</td><td> </td><td>Sum of ranks for end score = 4:</td><td> </td><td> </td><td style="font-weight:bold; text-align:right; ">11</td><td> </td><td> </td><td> </td></tr></tbody></table>
That is, I need a formula giving me 21, and another for giving 11...

 

[barry houdini]

Given your example you can use SUMIF formulas in E11:E12, e.g. in E11

=SUMIF(F2:F9,0,G2:G9)

and in E12

=SUMIF(F2:F9,"<>0",G2:G9)

or do you want to do the calculations without a RANK column? If so then you can use this formula for E11 that only references columns B and F

=SUMPRODUCT((F2:F9=0)+0,COUNTIF(B2:B9,">"&B2:B9)+1)

for E12 use the same formula but change the = to <>

 

[flouee]

Yes; I need to have formula that
1. selects specific rows in a data sheet (according to a set of criterias; in this example all mice with end score =0 or = 4),
2. calculates the rank (RANK) for some of these rows (a new set of criterias; in this example end score=0),
3. and then summarizes those ranks (21 in this example)

I cannot have a column with the rank of each mouse, since the rank will depend on which other mice are included in the selection (in this example endscore 0 or 4, but other times I might need to find the rank for those with 0 or 3, or 1 or 4 ).

 

[barry houdini]

OK, I edited my post to include formulas that will give you the required calculation without a RANK column.....but that assumes you are ranking all the "mice". Assuming you only want to rank some (e.g. those with scores 0 and 4 when there are other scores) then clearly you need another formula - which version of Excel are you using? In Excel 2007 this formula will sum all of the ranks associated with zero scores....assuming you are ranking all mice with scores of 4 and zero

=SUMPRODUCT(--(F2:F20=0),COUNTIFS(F2:F20,0,B2:B20,">"&B2:B20)+COUNTIFS(F2:F20,4,B2:B20,">"&B2:B20)+1)

The first zero is the score for which you want the sum of ranks....the second zero and the 4 indicate the scores that are ranked - change as required

 

[flouee]

Wohoo, I have to admit that I have no idea how it works, but it does! Thanks a lot!

If I would like to make a correction for those mice which get the same rank, how would I do that? The rank formula for the first mouse should be:

=RANK(B2;$B$2:$B$9;1)+(COUNT($B$2:$B$9) + 1 - RANK(B2;$B$2:$B$9;0) - RANK(B2;$B$2:$B$9; 1))/2

Can I incorporate this into the SUMPRODUCT formula? With this correction, the sum should be 22 instead of 21.

(Yeah, I use the 2007 version)

 

[barry houdini]

That would need to be like this I think

=SUMPRODUCT(--(F2:F20=0),COUNTIFS(F2:F20,0,B2:B20,">"&B2:B20)+COUNTIFS(F2:F20,4,B2:B20,">"&B2:B20)+COUNTIFS(F2:F20,0,B2:B20,B2:B20)/2+COUNTIFS(F2:F20,4,B2:B20,B2:B20)/2+0.5)