sumproduct RANK array (multiple columns) - with conditions

[collegeitdept]

Hello,

The rank function using sumproduct supports multiple conditions and is really handy.

I found out how to modify the RANK function to support arrays (multiple columns) using something like the following function:


=SUMPRODUCT((LEFT($A$1:$D$1,LEN($F1))=F$1)*(Premiere!$A$2:$A$20<>0),--($A$2:$D$20>$F2))+1



However, it doesn't seem to work with conditions:

[TABLE="width: 1820"]
<tbody>[TR]
[TD][/TD]
[TD]REGULAR
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Product Name
[/TD]
[TD]Kind
[/TD]
[TD]Grouping
[/TD]
[TD]Release Date
[/TD]
[TD]
[/TD]
[TD]Platform 5
[/TD]
[TD]RANK
[/TD]
[TD]Platform 1
[/TD]
[TD]RANK
[/TD]
[TD]Platform 2
[/TD]
[TD]RANK
[/TD]
[TD]Platform 3
[/TD]
[TD]RANK
[/TD]
[TD]Platform 4
[/TD]
[TD]RANK
[/TD]
[TD]Platform 6
[/TD]
[TD]RANK
[/TD]
[TD]Platform 7
[/TD]
[TD]RANK
[/TD]
[TD]Platform 8
[/TD]
[TD]RANK
[/TD]
[TD]Platform 9
[/TD]
[TD]Platform 10
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Name 2
[/TD]
[TD]REGULAR
[/TD]
[TD]1
[/TD]
[TD]1/14/2014
[/TD]
[TD]
[/TD]
[TD]12,283
[/TD]
[TD]1
[/TD]
[TD]44,600
[/TD]
[TD]1
[/TD]
[TD]48,498
[/TD]
[TD]1
[/TD]
[TD]75,502
[/TD]
[TD]1
[/TD]
[TD]3
[/TD]
[TD]1
[/TD]
[TD]
[/TD]
[TD]8
[/TD]
[TD]19,542
[/TD]
[TD]1
[/TD]
[TD]2,683
[/TD]
[TD]1
[/TD]
[TD]4,289
[/TD]
[TD]1,795
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Name 7
[/TD]
[TD]REGULAR
[/TD]
[TD]1
[/TD]
[TD]11/19/2013
[/TD]
[TD]
[/TD]
[TD]4,428
[/TD]
[TD]3
[/TD]
[TD]17,463
[/TD]
[TD]2
[/TD]
[TD]17,930
[/TD]
[TD]2
[/TD]
[TD]23,687
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[TD]5
[/TD]
[TD]1,013
[/TD]
[TD]4
[/TD]
[TD]1,240
[/TD]
[TD]4
[/TD]
[TD]93
[/TD]
[TD]7
[/TD]
[TD]115
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Name 11
[/TD]
[TD]REGULAR
[/TD]
[TD]1
[/TD]
[TD]9/24/2013
[/TD]
[TD]
[/TD]
[TD]2,528
[/TD]
[TD]4
[/TD]
[TD]10,523
[/TD]
[TD]3
[/TD]
[TD]11,088
[/TD]
[TD]3
[/TD]
[TD]16,085
[/TD]
[TD]3
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[TD]2,298
[/TD]
[TD]2
[/TD]
[TD]757
[/TD]
[TD]6
[/TD]
[TD]370
[/TD]
[TD]3
[/TD]
[TD]600
[/TD]
[TD]139
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Name 1
[/TD]
[TD]REGULAR
[/TD]
[TD]1
[/TD]
[TD]1/21/2014
[/TD]
[TD]
[/TD]
[TD]2,423
[/TD]
[TD]4
[/TD]
[TD]8,690
[/TD]
[TD]5
[/TD]
[TD]9,111
[/TD]
[TD]5
[/TD]
[TD]13,250
[/TD]
[TD]4
[/TD]
[TD]2
[/TD]
[TD]4
[/TD]
[TD]
[/TD]
[TD]8
[/TD]
[TD]2,071
[/TD]
[TD]3
[/TD]
[TD]407
[/TD]
[TD]2
[/TD]
[TD]1,110
[/TD]
[TD]605
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]Name 10
[/TD]
[TD]REGULAR
[/TD]
[TD]1
[/TD]
[TD]9/25/2013
[/TD]
[TD]
[/TD]
[TD]1,691
[/TD]
[TD]6
[/TD]
[TD]4,691
[/TD]
[TD]7
[/TD]
[TD]4,843
[/TD]
[TD]7
[/TD]
[TD]7,158
[/TD]
[TD]7
[/TD]
[TD]1
[/TD]
[TD]8
[/TD]
[TD]1,636
[/TD]
[TD]3
[/TD]
[TD]630
[/TD]
[TD]8
[/TD]
[TD]265
[/TD]
[TD]4
[/TD]
[TD]329
[/TD]
[TD]53
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]Name 12
[/TD]
[TD]REGULAR
[/TD]
[TD]1
[/TD]
[TD]9/24/2013
[/TD]
[TD]
[/TD]
[TD]1,373
[/TD]
[TD]6
[/TD]
[TD]7,835
[/TD]
[TD]6
[/TD]
[TD]8,025
[/TD]
[TD]6
[/TD]
[TD]10,035
[/TD]
[TD]6
[/TD]
[TD]1
[/TD]
[TD]7
[/TD]
[TD]2,791
[/TD]
[TD]1
[/TD]
[TD]804
[/TD]
[TD]5
[/TD]
[TD]257
[/TD]
[TD]5
[/TD]
[TD]424
[/TD]
[TD]134
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]Name 6
[/TD]
[TD]REGULAR
[/TD]
[TD]1
[/TD]
[TD]11/19/2013
[/TD]
[TD]
[/TD]
[TD]1,010
[/TD]
[TD]7
[/TD]
[TD]9,211
[/TD]
[TD]4
[/TD]
[TD]9,479
[/TD]
[TD]4
[/TD]
[TD]12,011
[/TD]
[TD]5
[/TD]
[TD]2
[/TD]
[TD]3
[/TD]
[TD]825
[/TD]
[TD]5
[/TD]
[TD]727
[/TD]
[TD]7
[/TD]
[TD]34
[/TD]
[TD]10
[/TD]
[TD]52
[/TD]
[TD]18
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]Name 5
[/TD]
[TD]REGULAR
[/TD]
[TD]1
[/TD]
[TD]11/20/2013
[/TD]
[TD]
[/TD]
[TD]627
[/TD]
[TD]10
[/TD]
[TD]942
[/TD]
[TD]10
[/TD]
[TD]985
[/TD]
[TD]9
[/TD]
[TD]1,641
[/TD]
[TD]9
[/TD]
[TD]1
[/TD]
[TD]10
[/TD]
[TD]797
[/TD]
[TD]6
[/TD]
[TD]60
[/TD]
[TD]10
[/TD]
[TD]165
[/TD]
[TD]6
[/TD]
[TD]197
[/TD]
[TD]29
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]Name 9
[/TD]
[TD]REGULAR
[/TD]
[TD]1
[/TD]
[TD]9/25/2013
[/TD]
[TD]
[/TD]
[TD]413
[/TD]
[TD]13
[/TD]
[TD]2,805
[/TD]
[TD]8
[/TD]
[TD]2,873
[/TD]
[TD]8
[/TD]
[TD]4,107
[/TD]
[TD]8
[/TD]
[TD]2
[/TD]
[TD]6
[/TD]
[TD]
[/TD]
[TD]8
[/TD]
[TD]17,655
[/TD]
[TD]2
[/TD]
[TD]57
[/TD]
[TD]8
[/TD]
[TD]83
[/TD]
[TD]36
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]Name 4
[/TD]
[TD]REGULAR
[/TD]
[TD]1
[/TD]
[TD]11/20/2013
[/TD]
[TD]
[/TD]
[TD]309
[/TD]
[TD]15
[/TD]
[TD]588
[/TD]
[TD]12
[/TD]
[TD]618
[/TD]
[TD]10
[/TD]
[TD]945
[/TD]
[TD]10
[/TD]
[TD]1
[/TD]
[TD]9
[/TD]
[TD]504
[/TD]
[TD]7
[/TD]
[TD]73
[/TD]
[TD]9
[/TD]
[TD]41
[/TD]
[TD]9
[/TD]
[TD]66
[/TD]
[TD]23
[/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]13
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]14
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]15
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]16
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]17
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]


The RANK column in RED is not correct - the function is generating wrong rank position numbers.

Here is the formula in that column:

=SUMPRODUCT(('Release Day'!$C$2:$C$200=$B$15)*(LEFT('Release Day'!$I$1:$U$1,LEN(G$16))=G$16),--('Release Day'!$I$2:$U$200>G17))+1


What this rank formula is doing is combining the numbers for "Platform 5A" and "Platform 5B" and from that sum - provide the rank position for "REGULAR" products only.




Here is the raw data: (in 'Release Day' tab of Excel workbook)


[TABLE="width: 1823"]
<colgroup><col><col><col><col><col><col><col><col><col><col span="15"></colgroup><tbody>[TR]
[TD]Product Name[/TD]
[TD]Unit[/TD]
[TD]Kind[/TD]
[TD]Metrics[/TD]
[TD]Sale Units[/TD]
[TD]Grouping[/TD]
[TD]Release Date[/TD]
[TD]Expiration Date[/TD]
[TD]Platform 1[/TD]
[TD]Platform 2[/TD]
[TD]Platform 3[/TD]
[TD]Platform 4[/TD]
[TD]Platform 5A[/TD]
[TD]Platform 5B[/TD]
[TD]Platform 6[/TD]
[TD]Platform 7[/TD]
[TD]Platform 8[/TD]
[TD]Platform 9[/TD]
[TD]Platform 10[/TD]
[TD]Platform 11[/TD]
[TD]Platform 12[/TD]
[TD]Proper Date[/TD]
[TD]Regular - platform 5 RANK[/TD]
[TD]SPECIAL - platform 5 RANK[/TD]
[/TR]
[TR]
[TD]Name 1[/TD]
[TD]Network[/TD]
[TD]REGULAR[/TD]
[TD]Sales Metrics[/TD]
[TD]Palette[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1/21/2014[/TD]
[TD="align: right"]1/21/2014[/TD]
[TD="align: right"]8690[/TD]
[TD="align: right"]9111[/TD]
[TD="align: right"]13250[/TD]
[TD="align: right"]1.75[/TD]
[TD="align: right"]1738[/TD]
[TD="align: right"]685[/TD]
[TD][/TD]
[TD="align: right"]2071[/TD]
[TD="align: right"]407[/TD]
[TD="align: right"]1110[/TD]
[TD="align: right"]605[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD]01-21-2014[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Name 2[/TD]
[TD]Network[/TD]
[TD]REGULAR[/TD]
[TD]Sales Metrics[/TD]
[TD]Palette[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1/14/2014[/TD]
[TD="align: right"]1/14/2014[/TD]
[TD="align: right"]44600[/TD]
[TD="align: right"]48498[/TD]
[TD="align: right"]75502[/TD]
[TD="align: right"]2.64[/TD]
[TD="align: right"]7295[/TD]
[TD="align: right"]4988[/TD]
[TD][/TD]
[TD="align: right"]19542[/TD]
[TD="align: right"]2,683[/TD]
[TD="align: right"]4289[/TD]
[TD="align: right"]1795[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD]01-14-2014[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Name 3[/TD]
[TD]Network[/TD]
[TD]SPECIAL[/TD]
[TD]Sales Metrics[/TD]
[TD]Palette[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]12/5/2013[/TD]
[TD="align: right"]12/5/2013[/TD]
[TD="align: right"]634[/TD]
[TD="align: right"]666[/TD]
[TD="align: right"]1142[/TD]
[TD="align: right"]1.44[/TD]
[TD="align: right"]196[/TD]
[TD="align: right"]96[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]12-05-2013[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Name 4[/TD]
[TD]Network[/TD]
[TD]REGULAR[/TD]
[TD]Sales Metrics[/TD]
[TD]Palette[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]11/20/2013[/TD]
[TD="align: right"]12/25/2013[/TD]
[TD="align: right"]588[/TD]
[TD="align: right"]618[/TD]
[TD="align: right"]945[/TD]
[TD="align: right"]1.14[/TD]
[TD="align: right"]260[/TD]
[TD="align: right"]49[/TD]
[TD="align: right"]504[/TD]
[TD="align: right"]73[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]66[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD]11-20-2013[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Name 5[/TD]
[TD]Network[/TD]
[TD]REGULAR[/TD]
[TD]Sales Metrics[/TD]
[TD]Palette[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]11/20/2013[/TD]
[TD="align: right"]12/25/2013[/TD]
[TD="align: right"]942[/TD]
[TD="align: right"]985[/TD]
[TD="align: right"]1641[/TD]
[TD="align: right"]1.13[/TD]
[TD="align: right"]480[/TD]
[TD="align: right"]147[/TD]
[TD="align: right"]797[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]165[/TD]
[TD="align: right"]197[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD]11-20-2013[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Name 6[/TD]
[TD]Network[/TD]
[TD]REGULAR[/TD]
[TD]Sales Metrics[/TD]
[TD]Palette[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]11/19/2013[/TD]
[TD="align: right"]12/24/2013[/TD]
[TD="align: right"]9211[/TD]
[TD="align: right"]9479[/TD]
[TD="align: right"]12011[/TD]
[TD="align: right"]2.09[/TD]
[TD="align: right"]799[/TD]
[TD="align: right"]211[/TD]
[TD="align: right"]825[/TD]
[TD="align: right"]727[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]52[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD]11-19-2013[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Name 7[/TD]
[TD]Network[/TD]
[TD]REGULAR[/TD]
[TD]Sales Metrics[/TD]
[TD]Palette[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]11/19/2013[/TD]
[TD="align: right"]12/24/2013[/TD]
[TD="align: right"]17463[/TD]
[TD="align: right"]17930[/TD]
[TD="align: right"]23687[/TD]
[TD="align: right"]1.68[/TD]
[TD="align: right"]3836[/TD]
[TD="align: right"]592[/TD]
[TD="align: right"]1013[/TD]
[TD="align: right"]1240[/TD]
[TD="align: right"]93[/TD]
[TD="align: right"]115[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD]11-19-2013[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Name 8[/TD]
[TD]Network[/TD]
[TD]SPECIAL[/TD]
[TD]Sales Metrics[/TD]
[TD]Palette[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]11/19/2013[/TD]
[TD="align: right"]12/3/2013[/TD]
[TD="align: right"]694[/TD]
[TD="align: right"]724[/TD]
[TD="align: right"]997[/TD]
[TD="align: right"]1.22[/TD]
[TD="align: right"]829[/TD]
[TD="align: right"]480[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]31[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD]11-19-2013[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Name 9[/TD]
[TD]Network[/TD]
[TD]REGULAR[/TD]
[TD]Sales Metrics[/TD]
[TD]Palette[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9/25/2013[/TD]
[TD="align: right"]11/13/2013[/TD]
[TD="align: right"]2805[/TD]
[TD="align: right"]2873[/TD]
[TD="align: right"]4107[/TD]
[TD="align: right"]1.5[/TD]
[TD="align: right"]285[/TD]
[TD="align: right"]128[/TD]
[TD][/TD]
[TD="align: right"]17655[/TD]
[TD="align: right"]57[/TD]
[TD="align: right"]83[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD]09-25-2013[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Name 10[/TD]
[TD]Network[/TD]
[TD]REGULAR[/TD]
[TD]Sales Metrics[/TD]
[TD]Palette[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9/25/2013[/TD]
[TD="align: right"]11/27/2013[/TD]
[TD="align: right"]4691[/TD]
[TD="align: right"]4843[/TD]
[TD="align: right"]7158[/TD]
[TD="align: right"]1.36[/TD]
[TD="align: right"]1305[/TD]
[TD="align: right"]386[/TD]
[TD="align: right"]1636[/TD]
[TD="align: right"]630[/TD]
[TD="align: right"]265[/TD]
[TD="align: right"]329[/TD]
[TD="align: right"]53[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD]09-25-2013[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Name 11[/TD]
[TD]Network[/TD]
[TD]REGULAR[/TD]
[TD]Sales Metrics[/TD]
[TD]Palette[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9/24/2013[/TD]
[TD="align: right"]11/5/2013[/TD]
[TD="align: right"]10523[/TD]
[TD="align: right"]11088[/TD]
[TD="align: right"]16085[/TD]
[TD="align: right"]2.47[/TD]
[TD="align: right"]2045[/TD]
[TD="align: right"]483[/TD]
[TD="align: right"]2298[/TD]
[TD="align: right"]757[/TD]
[TD="align: right"]370[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]139[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD]09-24-2013[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Name 12[/TD]
[TD]Network[/TD]
[TD]REGULAR[/TD]
[TD]Sales Metrics[/TD]
[TD]Palette[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9/24/2013[/TD]
[TD="align: right"]11/12/2013[/TD]
[TD="align: right"]7835[/TD]
[TD="align: right"]8025[/TD]
[TD="align: right"]10035[/TD]
[TD="align: right"]1.37[/TD]
[TD="align: right"]1007[/TD]
[TD="align: right"]366[/TD]
[TD="align: right"]2791[/TD]
[TD="align: right"]804[/TD]
[TD="align: right"]257[/TD]
[TD="align: right"]424[/TD]
[TD="align: right"]134[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD]09-24-2013[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Name 13[/TD]
[TD]Network[/TD]
[TD]SPECIAL[/TD]
[TD]Sales Metrics[/TD]
[TD]Palette[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9/23/2013[/TD]
[TD="align: right"]9/23/2013[/TD]
[TD="align: right"]1476[/TD]
[TD="align: right"]1507[/TD]
[TD="align: right"]1851[/TD]
[TD="align: right"]0.62[/TD]
[TD="align: right"]506[/TD]
[TD="align: right"]91[/TD]
[TD][/TD]
[TD="align: right"]812[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]09-23-2013[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Name 14[/TD]
[TD]Network[/TD]
[TD]SPECIAL[/TD]
[TD]Sales Metrics[/TD]
[TD]Palette[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1/17/2014[/TD]
[TD="align: right"]1/17/2014[/TD]
[TD="align: right"]1032[/TD]
[TD="align: right"]1067[/TD]
[TD="align: right"]1555[/TD]
[TD="align: right"]0.58[/TD]
[TD="align: right"]738[/TD]
[TD="align: right"]77[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD]01-17-2014[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[/TR]
</tbody>[/TABLE]



I was wondering if someone could kindly help please modify this sumproduct RANK function to support conditions and arrays?


Excel file:
https://dl.dropboxusercontent.com/u...t Rank condition - away multiple columns.xlsx



Thanks.

 

[barry houdini]

Why do you have to go back to the base data? Aren't you just ranking the values in the Platform 5 column with conditions, wouldn't this work?

=COUNTIFS(C$17:C$26,C17,G$17:G$26,">"&G17)+1

 

[collegeitdept]

Why do you have to go back to the base data? Aren't you just ranking the values in the Platform 5 column with conditions, wouldn't this work?

=COUNTIFS(C$17:C$26,C17,G$17:G$26,">"&G17)+1

Hi Barry,

I have to keep going back to the base data to apply that same formula to all other "RANK" columns.... so it can rank based on the appropriate Platform.

IE: Platform 1 rank
platform 2 rank
platform 3
platform 4
platform 5 (A & B)
Platform 6
etc...

Thanks.

 

[barry houdini]

I still don't see that.

Given that you actually have all REGULAR values in one table it doesn't even have to be as complicated as the COUNTIFS function I suggested, can't you use a simple RANK function in H17 copied down?

=IF(G17=0,"",RANK(G17,G$17:G$33))

That can be copied to all other RANK columns

In fact, it doesn't seem like your ranks are correct for Platform 1 either - ranks go up to 12 but with 9 and 11 missing - (presumably because they are confused with values for Platform 10), so that version would fix that issue too.

The reason why your platform 5 version doesn't work is because it is trying to rank against the individual 5A and 5B values rather than the total 5A and 5B values for each product name

There may be a way to fix that but as you also need to fix the Platform 1/Platform 10 issue I can't seem any simpler way than just ranking those values as described above.

If that formula doesn't work can you explain which results are wrong so that I can better understand the issue