A Counting Problem

[ouadad]

This one has got me puzzled and I'm sure there is a simple solution. I have a long list of rankings for various statistical tests, i.e.,

A 1
B 2
C 3
A 2
B 3
C 1

and so on. I just want to determine how many 1st, 2nd and 3rd place rankings of A, then of B, etc. There is no real range to use a Countif function and I thought I could do this without VBA.

 

[Joe4]

Take a look at COUNTIFS, which allows for multiple criteria: https://www.techonthenet.com/excel/formulas/countifs.php

 

[Peter_SSs]

What result(s) would you want for that sample data and why?

 

[ouadad]

For that sample data:

A would have 1 1st Choice and 1 second choice
B would have 1 second choice and 1 third choice
C would have 1 3rd choice and 1 first choice


I running various statistical tests on out-of-sample forecasts using a number of methods, i.e., Mean, naive, 3rd party software, and I want to be able to determine which method provides a best forecast based on the out-of-sample tests like sMAPE, MASE, etc.

 

[Peter_SSs]

Ok, well you certainly could use COUNTIFS as suggested by Joe and as I have used in columns E:H (formula in F2 is copied across and down)

Alternatively, you could possibly use Excel's built-in Pivot Table feature (on the Insert ribbon tab) as I have shown in columns J:N

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Item	Rank				1	2	3		Count of Rank	Rank
2	A	1			A	1	1	0		Item	1	2	3	Grand Total
3	B	2			B	0	1	1		A	1	1		2
4	C	3			C	1	0	1		B		1	1	2
5	A	2								C	1		1	2
6	B	3								Grand Total	2	2	2	6
7	C	1
Count

 

[ouadad]

Thank you very much Joe and Peter. This is excellent.

 

[Peter_SSs]

Thank you very much Joe and Peter. This is excellent.

You are very welcome. Thanks for letting us know.