COUNTIFS Formula based on changing amounts

J

James8761

Board Regular
Joined
Apr 24, 2012
Messages
154
Office Version
  1. 2019
Platform
  1. Windows
Hi,
I am trying to add another formula to my spreadsheet. This is concerned with where a runner finishes. We give points to runners who win races and who are 'placed' in a race. If they win a race that just goes in the win column and not in the place column.

If there are 10 or less runners in a race we only give points to the winner, there are no places.
If there are up to 20 runners in a race we give points to the winner, and second place gets placed.
If there are up to 30 runners in a race we give points to the winner, second place and third place gets placed.
If there are more than 30 runners in a race we give points to the winner, second place, third place and fourth gets placed.

I've put together the outcome I would like in the table below. Ideally I want to get rid of Column D and just have a formula in Cell L2, where each time I change the name in Cell J1, it would calculate the amount of times a runner placed.

Thank you for any help.


Positions.xlsx
ABCDEFGHIJKL
1NamePositionParticipantsPlacedParticipantsPlacedNameJenkinsWonPlaced
2Jenkins24N10136
3Jenkins422N202
4Jenkins19N303
5Jenkins216Y404
6Jenkins335Y
7Jenkins28N
8Jenkins112N
9Jenkins17N
10Jenkins1140N
11Jenkins336Y
12Jenkins432Y
13Jenkins221Y
14Jenkins29N
15Jenkins327Y
16Davis34N
17Davis722N
18Davis29N
19Davis316N
20Davis235Y
21Davis18N
22Davis512N
23Davis37N
24Davis440Y
25Davis236Y
26Davis832N
27Davis421N
28Davis19N
29Davis227Y
Sheet1
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Sort by date Sort by votes
Could we change the lookup table in columns F:G a little?

With your version of Excel the formula may require confirmation with Ctrl+Shift+Enter, not just Enter

James8761.xlsm
ABCDEFGHIJKLM
1NamePositionParticipantsParticipantsPlacedNameJenkinsWonPlaced
2Jenkins24From To36
3Jenkins4221101
4Jenkins1911202
5Jenkins21621303
6Jenkins335314
7Jenkins28
8Jenkins112
9Jenkins17
10Jenkins1140
11Jenkins336
12Jenkins432
13Jenkins221
14Jenkins29
15Jenkins327
16Davis34
17Davis722
18Davis29
19Davis316
20Davis235
21Davis18
22Davis512
23Davis37
24Davis440
25Davis236
26Davis832
27Davis421
28Davis19
29Davis227
Sheet1
Cell Formulas
RangeFormula
L2L2=COUNTIFS(A2:A29,K1,B2:B29,1)
M2M2=SUMPRODUCT(--(A2:A29=K1),--(B2:B29<=VLOOKUP(C2:C29,F3:H6,3)))-L2
 
Upvote 0
Peter_SSs said:
Could we change the lookup table in columns F:G a little?

With your version of Excel the formula may require confirmation with Ctrl+Shift+Enter, not just Enter

James8761.xlsm
ABCDEFGHIJKLM
1NamePositionParticipantsParticipantsPlacedNameJenkinsWonPlaced
2Jenkins24From To36
3Jenkins4221101
4Jenkins1911202
5Jenkins21621303
6Jenkins335314
7Jenkins28
8Jenkins112
9Jenkins17
10Jenkins1140
11Jenkins336
12Jenkins432
13Jenkins221
14Jenkins29
15Jenkins327
16Davis34
17Davis722
18Davis29
19Davis316
20Davis235
21Davis18
22Davis512
23Davis37
24Davis440
25Davis236
26Davis832
27Davis421
28Davis19
29Davis227
Sheet1
Cell Formulas
RangeFormula
L2L2=COUNTIFS(A2:A29,K1,B2:B29,1)
M2M2=SUMPRODUCT(--(A2:A29=K1),--(B2:B29<=VLOOKUP(C2:C29,F3:H6,3)))-L2
Click to expand...
Hi,

Thanks for the reply. I've input the formula with ctrl, shift enter and just enter, but both answers return 0, as opposed 6.
 
Upvote 0
Did you insert the extra column and change the lookup table to match mine?
 
Upvote 0
James8761 said:
Hi,

Thanks for the reply. I've input the formula with ctrl, shift enter and just enter, but both answers return 0, as opposed 6.
Click to expand...
Hi Peter,

yes, I believe everything is as per your updated sheet, as below:

Positions.xlsx
ABCDEFGHIJKLM
1NamePositionParticipantsParticipantsPlacedNameDavisWonPlaced
2Jenkins24FromTo20
3Jenkins4221101
4Jenkins1911202
5Jenkins21621303
6Jenkins335314
7Jenkins28
8Jenkins112
9Jenkins17
10Jenkins1140
11Jenkins336
12Jenkins432
13Jenkins221
14Jenkins29
15Jenkins327
16Davis34
17Davis722
18Davis29
19Davis316
20Davis235
21Davis18
22Davis512
23Davis37
24Davis440
25Davis236
26Davis832
27Davis421
28Davis19
29Davis227
Sheet1
Cell Formulas
RangeFormula
L2L2=COUNTIFS(A2:A29,K1,B2:B29,1)
M2M2=SUMPRODUCT(--(A2:A29=K1),--(B2:B29<=VLOOKUP(C2:C29,F3:H6,3)))-L2
Press CTRL+SHIFT+ENTER to enter array formulas.


And Cell M2 has the formula in it:

Positions.xlsx
M
20
Sheet1
Cell Formulas
RangeFormula
M2M2=SUMPRODUCT(--(A2:A29=K1),--(B2:B29<=VLOOKUP(C2:C29,F3:H6,3)))-L2
Press CTRL+SHIFT+ENTER to enter array formulas.


=SUMPRODUCT(--(A2:A29=K1),--(B2:B29<=VLOOKUP(C2:C29,F3:H6,3)))-L2
 
Upvote 0
Hi Peter,

yes, I believe everything is as per your updated sheet, as below:

Positions.xlsx
ABCDEFGHIJKLM
1NamePositionParticipantsParticipantsPlacedNameJenkinsWonPlaced
2Jenkins24FromTo30
3Jenkins4221101
4Jenkins1911202
5Jenkins21621303
6Jenkins335314
7Jenkins28
8Jenkins112
9Jenkins17
10Jenkins1140
11Jenkins336
12Jenkins432
13Jenkins221
14Jenkins29
15Jenkins327
16Davis34
17Davis722
18Davis29
19Davis316
20Davis235
21Davis18
22Davis512
23Davis37
24Davis440
25Davis236
26Davis832
27Davis421
28Davis19
29Davis227
Sheet1
Cell Formulas
RangeFormula
L2L2=COUNTIFS(A2:A29,K1,B2:B29,1)
M2M2=SUMPRODUCT(--(A2:A29=K1),--(B2:B29<=VLOOKUP(C2:C29,F3:H6,3)))-L2
Press CTRL+SHIFT+ENTER to enter array formulas.



And the formula is in Cell M2.


Positions.xlsx
M
1Placed
20
Sheet1
Cell Formulas
RangeFormula
M2M2=SUMPRODUCT(--(A2:A29=K1),--(B2:B29<=VLOOKUP(C2:C29,F3:H6,3)))-L2
Press CTRL+SHIFT+ENTER to enter array formulas.


1636798989601.png
 
Upvote 0
Hmm, I don't have 2019 to test with but with MS365 it does produce zero for me too if I use C+S+E but the correct result with normal Entry.

This may well do the same thing but can you try this in M2 with normal entry and if still zero then re-confirm with C+S+E?

Excel Formula: 
=SUMPRODUCT(--(A2:A29=K1),--(B2:B29<=MATCH(C2:C29,F3:F6)))-L2
 
Upvote 0
Solution
BTW, note that you don't need to repeat the M2 formula in your posts as it already gets produced at the bottom of the mini-sheet
 
Upvote 0
Peter_SSs said:
Hmm, I don't have 2019 to test with but with MS365 it does produce zero for me too if I use C+S+E but the correct result with normal Entry.

This may well do the same thing but can you try this in M2 with normal entry and if still zero then re-confirm with C+S+E?

Excel Formula: 
=SUMPRODUCT(--(A2:A29=K1),--(B2:B29<=MATCH(C2:C29,F3:F6)))-L2
Click to expand...
Thanks very much for the feedback Peter and thank you for the above formula. This one does exactly as I need.

Many thanks
James
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Solved
Complex tiebreaker
Replies
7
Views
299
HorizonHunter
H
K
  • Solved
Sports Carnival Scoring
Replies
4
Views
444
fjns
F
D
  • Question
Results by handicap - with ties. Look up by score without duplicates
Replies
4
Views
97
dkjonesau
D
N
  • Question
Auto Sort for Leaderboard
Replies
0
Views
115
NTLKnight
N
C
  • Solved
Possible Index Match Count Formula Help
Replies
7
Views
421
SanjayGMusafir
SanjayGMusafir

Forum statistics

Threads
1,215,110
Messages
6,123,147
Members
449,098
Latest member
Doanvanhieu

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top