Rank Excluding Text

[adrienne0914]

I want to rank sales by location, but I want to exclude all origins from China, which is the country. I'm only interested in the activity from the city (lines 18-20, which display the desired results). With the formula as it is, it's considering everything. Is there a way to rank excluding China? Maybe RANK is the wrong formula...

Excel 2010

	A	B	C	D	E	F	G	H
1	ORIGIN	DESTINATION	TRADE LANE	TERRITORY	PARTY	TOTALS	Chicago Sales	Chicago Rank
2	China	Chicago	China - Chicago	Mid West	ABATA	64	64	16
3	China	Chicago	China - Chicago	Mid West	BLOGTAG	1,039	1,039	-
4	China	Chicago	China - Chicago	Mid West	COGIBOX	901	901	-
5	China	Chicago	China - Chicago	Mid West	CAMIDO	201	201	-
6	China	Chicago	China - Chicago	Mid West	DABJAM	4	4	-
7	China	Chicago	China - Chicago	Mid West	DEMIZZ	765	765	-
8	China	Chicago	China - Chicago	Mid West	DIVAPE	470	470	-
9	China	Chicago	China - Chicago	Mid West	FANOODLE	1,669	1,669	-
10	China	Chicago	China - Chicago	Mid West	GEBA	39	39	-
11	China	Chicago	China - Chicago	Mid West	INNOJAM	423	423	-
12	China	Chicago	China - Chicago	Mid West	JABBERBEAN	12	12	-
13	China	Chicago	China - Chicago	Mid West	KAZU	81	81	-
14	China	Chicago	China - Chicago	Mid West	PODCAT	39	39	-
15	China	Chicago	China - Chicago	Mid West	STUDZ	510	510	-
16	China	Chicago	China - Chicago	Mid West	SUCKERZ	344	344	-
17	China	Chicago	China - Chicago	Mid West	TOODLES	156	156	-
18	Shanghai	Chicago	Shanghai - Chicago	Mid West	BLOGTAG	334	334	1
19	Shanghai	Chicago	Shanghai - Chicago	Mid West	COGIBOX	32	32	5
20	Shanghai	Chicago	Shanghai - Chicago	Mid West	CAMIDO	69	69	4
21	Shanghai	Chicago	Shanghai - Chicago	Mid West	DEMIZZ	143	143	3
22	Shanghai	Chicago	Shanghai - Chicago	Mid West	FANOODLE	14	14	6
23	Shanghai	Chicago	Shanghai - Chicago	Mid West	SUCKERZ	182	182	2
24	Shanghai	Chicago	Shanghai - Chicago	Mid West	TOODLES	2	2	7

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula H2 =RANK(G2,G$2:G$24,0)+COUNTIF(G$2:G24,G24)-1 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[Peter_SSs]

Try ..

Excel Workbook
	A	G	H
1	ORIGIN	Chicago sales	Chicago Rank
2	China	64	-
3	China	1,039	-
4	China	901	-
5	China	201	-
6	China	4	-
7	China	765	-
8	China	470	-
9	China	1,669	-
10	China	39	-
11	China	423	-
12	China	12	-
13	China	81	-
14	China	39	-
15	China	510	-
16	China	344	-
17	China	156	-
18	Shanghai	334	1
19	Shanghai	32	5
20	Shanghai	69	4
21	Shanghai	143	3
22	Shanghai	14	6
23	Shanghai	182	2
24	Shanghai	2	7
Rank

 

[adrienne0914]

Try ..

Rank

	A	G	H
1	ORIGIN	Chicago sales	Chicago Rank
2	China	64	-
3	China	1,039	-
4	China	901	-
5	China	201	-
6	China	4	-
7	China	765	-
8	China	470	-
9	China	1,669	-
10	China	39	-
11	China	423	-
12	China	12	-
13	China	81	-
14	China	39	-
15	China	510	-
16	China	344	-
17	China	156	-
18	Shanghai	334	1
19	Shanghai	32	5
20	Shanghai	69	4
21	Shanghai	143	3
22	Shanghai	14	6
23	Shanghai	182	2
24	Shanghai	2	7

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:73px;"><col style="width:102px;"><col style="width:101px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula H2 =IF(A2="China","-",SUMPRODUCT(($A$2:$A$24<>"China")*(G2<$G$2:$G$24))+1) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Peter, thank you so much. This worked GREAT!! I love this forum!!

 

[Peter_SSs]

Peter, thank you so much. This worked GREAT!! I love this forum!!

You're welcome. Glad it worked for you.

BTW, best not to fully quote long posts as it makes the thread harder to read/navigate. If you want to quote, quote small, relevant parts only.

 

[adrienne0914]

Understood. I tried to just quote the formula that you provided, but it didn't work out. I'll get it together. Thanks!