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:

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

<tbody>
</tbody>

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)

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

<colgroup><col><col><col><col><col><col><col><col><col><col span="15"></colgroup><tbody>
</tbody>

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