Counting the frequency of a value used in a formula

FayeWray

New Member
Joined
Aug 21, 2018
Messages
1
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.

HistoryBiologyComputingMathsGeographyArtHIGHEST
567799
4564446

<tbody>
</tbody>

So I want a table like:

History0
Biology0
Computing1
Maths0
Geography0
Art1

<tbody>
</tbody>

Any advice about formulas etc would be appreciated.

Thanks!
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,600
Welcome to the MrExcel forum.

Try:

ABCDEFGHIJKL
1HistoryBiologyComputingMathsGeographyArtHIGHESTSubjectPicked largest
2History1
31234324Biology0
44564446Computing1
55267799Maths3
68432578Geography0
78319399Art2
84368458
9

<tbody>
</tbody>
Sheet4

Worksheet Formulas
CellFormula
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,096,454
Messages
5,450,555
Members
405,616
Latest member
awiltz

This Week's Hot Topics

Top