CODEWITHSUNDEEP
excel
LiamG
LiamG

Reputation: 68

Countif conditional criteria

I have a set of data with the max value for each col in the top row I want to count how many times each row has the highest value for the col

Right now im trying =COUNTIF(B3:I3,"="&B$2) but it keeps the value remains at B2 and always just checks if the value is 84

Is this possible with one simple line? 

              A   B   C   D   E   F   G   H   I
            Max 84  85  84  82  88  81  89  81
           wins
    bob      0  42  52  45  59  55  69  60  70
    joe      0  64  60  61  57  57  53  60  56
    ron      1  82  85  77  74  81  78  74  77
    fred     0  50  59  54  65  62  73  62  71
    jon      2  84  81  82  72  88  78  76  73
    evan     1  74  82  73  75  79  81  68  76
    alex     4  83  75  84  82  78  76  89  81

Upvotes: 0

Views: 65

Answers (2)

chris neilsen
chris neilsen

Reputation: 53166

As a non-array formula (complete with a regular Enter)

=SUMPRODUCT(--(B4:I4=$B$2:$I$2))

Upvotes: 2

Scott Craner
Scott Craner

Reputation: 152660

Use:

=SUM(IF(B3:I3=$B$1:$I$1,1,0))

Put in A3. It is an array formula, confirm with Ctrl-Shift-Enter. Then copy down.

enter image description here

Upvotes: 1

Related Questions