# Counting the frequency of a value used in a formula

#### FayeWray

##### New Member
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!

### 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
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.