# help!

#### rndgirl

##### New Member
HI, i am tryin to make a spreadsheet for my sports club. I know how to find the highest scoring player but would like a formula that would tell me who the highest scoring male/female/coach was and what they scored. could anyone provide me with a formula??

### Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
If you have the category, male/female etc. in column A and score in column B then to find the highest scoring female player

=MAX(IF(A1:A100="female",B1:B100))

confirmed with CTRL+SHIFT+ENTER

this hasn't worked, the male/female category is in column D and their scores are in column J, i hvae tried changing it but it still doesnt work.

rndgirl

What result do you get?

You need to confirm with CTRL+SHIFT+ENTER. Select cell with formula, press F2 then hold down CTRL and SHIFT buttons and press ENTER. Curly braces like { should appear around the formula in the formula bar.

yep that happens, but it says microsoft excel cannot calculate formula.

this is what i ahve changed the formula to

=MAX(IF(D1:D100="female",J1:J100))

Not sure why that doesn't work - it works for me - does yours look like this?
Book1
CDEFGHIJK
1female0
2male1
3female82
4male3
5female4
6male5
7female6
8male7
9female8
10male9
11
Sheet3

formula in G3

=MAX(IF(D1:D100="female",J1:J100))

confirmed with CTRL+SHIFT+ENTER

You could also try it without an array formula (without CTRL+SHIFT+ENTER)....

=SUMPRODUCT(MAX((D1:D100="female")*((J1:J100))))

Nope that doesnt work either

Here is a rough copy of my spreadsheet, i need formulas for the cells highlighted in yellow. I don't mind if it shows just the score but would like the name to come up too.

[/img]

rndgirl

For the data posted, I believe that you need Barry's formula adjusted as follows. This formula in cell I26 for Best Female:
=MAX(IF(D1:D20="Female",I1:I20))
confirmed with Ctrl+Shift+Enter

In I23 for Best Team:
=MAX(IF(H1:H20="Team Total",I1:I20))
confirmed with Ctrl+Shift+Enter
etc

Replies
1
Views
155
Replies
8
Views
280
Replies
1
Views
223
Replies
2
Views
211
Replies
1
Views
157

1,218,520
Messages
6,142,941
Members
450,456
Latest member
Exidur

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back