Counting the frequency of a value used in a formula

[FayeWray]

Hi all,

I'm trying to calculate the frequency a value is used in a formula. I have a range of results (ranging numerically from 1-9) each connected to a topic/subject. I have created a cell which identifies the largest number amongst those results in every row (=LARGE(S5:AN5,{1{)).

What i'm trying to calculate is the frequency of which particular topics/subjects are identified as the largest.

e.g. in row 5, '9' is identified as the highest value. This is under the subject 'Computing'. In row 6, '8' is identified as the largest number and this is under the subject 'History'. What i'd like to know is how frequently 'History' or 'Computing' is identified as the largest value in the set of results.

History	Biology	Computing	Maths	Geography	Art	HIGHEST
5		6	7	7	9	9
4	5	6	4	4	4	6

<tbody>
</tbody>

So I want a table like:

History	0
Biology	0
Computing	1
Maths	0
Geography	0
Art	1

<tbody>
</tbody>

Any advice about formulas etc would be appreciated.

Thanks!

 

[Eric W]

Welcome to the MrExcel forum.

Try:

	A	B	C	D	E	F	G	H	I	J	K	L
1	History	Biology	Computing	Maths	Geography	Art	HIGHEST			Subject	Picked largest
2										History	1
3	1	2	3	4	3	2	4			Biology	0
4	4	5	6	4	4	4	6			Computing	1
5	5	2	6	7	7	9	9			Maths	3
6	8	4	3	2	5	7	8			Geography	0
7	8	3	1	9	3	9	9			Art	2
8	4	3	6	8	4	5	8
9

<tbody>
</tbody>

Sheet4​

Worksheet Formulas Cell Formula K2 =SUMPRODUCT(--(INDEX($A$2:$F$10,0,MATCH(J2,$A$1:$F$1,0))=$G$2:$G$10),--($G$2:$G$10<>"")) <tbody> </tbody>

<tbody>
</tbody>

Change your ranges to match. Note that if there's a tie, like on row 7 here with Maths and Art, both will be counted.

 

[jtakw]

Cross posted here: https://www.excelforum.com/excel-fo...e-frequency-of-a-value-used-in-a-formula.html

 

[Macropod]

FayeWray: Please read Mr Excel's policy on Cross-Posting in rule 13: http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html