Using COUNTIF with a dropdown menu

kcross31

New Member
Joined
Jun 21, 2021
Messages
14
Office Version
  1. 365
Platform
  1. Windows
  2. Web
Hello, I thought I had my workbook figured out but it seems I am way off. There are quite a few things I am trying to accomplish with this but it all revolves around selections made from a dropdown menu.

1642018100822.png

The orange dropdown menus contains 7 selections seen in column N.

First: If one of the first 6 are selected, Fall 6, Tech Fall 5, Major Decision 4, Decision 3, Injury Default 6, and Disqualification 6, I want a single count added to the green cell (A2), if the last option is selected, Loss 0, then I want a single count added to the pink cell (B2). This is to keep track of wins and losses.

Next: I'd like the win percentage calculated in the blue cell (D2)

Finally: I'd like total points to be calculated based on how the participant won. These are the numbers that are with the text in column N. So for example if a participant wins by Fall 6 then 6 points will be added to the yellow cell (E2)

There are more things I will eventually need to accomplish but this will get me where I need to be if anyone can help!? Thanks in advance for your time.
 

Attachments

  • 1642018085216.png
    1642018085216.png
    11.3 KB · Views: 8

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Book1
ABCDEFGHIJKLM
1winslossesrankwin%total pointsmatch 1match 2match 3match 4match 5fall 55
23175%14tech fall 5loss 0injury default 6decision 3tech fall 55
3major decision 44
4decision 33
5injury default 66
6disqualification 66
7loss 00
Sheet1
Cell Formulas
RangeFormula
A2A2=SUM(COUNTIF(F2:J2,L1:L6))
B2B2=SUM(COUNTIF(F2:J2,L7))
D2D2=A2/SUM(A2:B2)
E2E2=SUM(IF(F2="",0,VLOOKUP(F2,L1:M7,2,FALSE)),IF(G2="",0,VLOOKUP(G2,L1:M7,2,FALSE)),IF(H2="",0,VLOOKUP(H2,L1:M7,2,FALSE)),IF(I2="",0,VLOOKUP(I2,L1:M7,2,FALSE)),IF(J2="",0,VLOOKUP(J2,L1:M7,2,FALSE)))
Cells with Data Validation
CellAllowCriteria
F2:J2List=$L$1:$L$7
 
Upvote 0
Based on the information provided, in B2
Excel Formula:
=COUNTIF(F2:J2,"Loss*")
In A2
Excel Formula:
=COUNTIFS(F2:J2,"?*",F2:J2,"<>Loss*")
In D2
Excel Formula:
=A2/(A2+B2)
In E2
Excel Formula:
=SUM(--RIGHT(F2:J2))
 
Upvote 0
added a second choice for how to get the total points. also to mention, both of these require a second column with the results giving the point each are worth
-------------
Using COUNTIF with a dropdown menu.xlsx
ABCDEFGHIJKLM
1winslossesrankwin%total pointsmatch 1match 2match 3match 4match 5fall 55
23175%14tech fall 5loss 0injury default 6decision 3tech fall 55
32250%12injury default 6loss 0injury default 6loss 0major decision 44
4decision 33
5injury default 66
6disqualification 66
7loss 00
Sheet1
Cell Formulas
RangeFormula
A2:A3A2=SUM(COUNTIF(F2:J2,L$1:L$6))
B2:B3B2=SUM(COUNTIF(F2:J2,L$7))
D2:D3D2=A2/SUM(A2:B2)
E2E2=SUM(IF(F2="",0,VLOOKUP(F2,L1:M7,2,FALSE)),IF(G2="",0,VLOOKUP(G2,L1:M7,2,FALSE)),IF(H2="",0,VLOOKUP(H2,L1:M7,2,FALSE)),IF(I2="",0,VLOOKUP(I2,L1:M7,2,FALSE)),IF(J2="",0,VLOOKUP(J2,L1:M7,2,FALSE)))
E3E3=SUM(SUMIFS(M1:M7,L1:L7,F3:J3))
Cells with Data Validation
CellAllowCriteria
F2:J3List=$L$1:$L$7
 
Upvote 0
My suggestion
To simplify the formulas add a column to store only the points (see column N)

Pasta1
ABCDEFGHIJKLMN
1WinsLossesRankWin %Total PointsMatch 1Match 2Match 3Match 4Match 5Fall 66
23175%15Major Decision 4Loss 0Tech Fall 5Disqualification 6Tech Fall 55
33260%15Loss 0Fall 6Injury Default 6Decision 3Loss 0Major Decision 44
44180%20Tech Fall 5Disqualification 6Tech Fall 5Loss 0Major Decision 4Decision 33
5Injury Default 66
6Disqualification 66
7Loss 00
8
Plan3
Cell Formulas
RangeFormula
A2:A4A2=COUNTIFS($F2:$J2,"<>Loss 0",$F2:$J2,"<>")
B2:B4B2=COUNTIF($F2:$J2,"Loss 0")
D2:D4D2=A2/SUM(A2:B2)
E2:E4E2=SUMPRODUCT(SUMIF($M$1:$M$7,$F2:$J2,$N$1:$N$7))
Cells with Data Validation
CellAllowCriteria
F2:J4List=$M$1:$M$7


M.
 
Upvote 0
Book1
ABCDEFGHIJKLM
1winslossesrankwin%total pointsmatch 1match 2match 3match 4match 5fall 55
23175%14tech fall 5loss 0injury default 6decision 3tech fall 55
3major decision 44
4decision 33
5injury default 66
6disqualification 66
7loss 00
Sheet1
Cell Formulas
RangeFormula
A2A2=SUM(COUNTIF(F2:J2,L1:L6))
B2B2=SUM(COUNTIF(F2:J2,L7))
D2D2=A2/SUM(A2:B2)
E2E2=SUM(IF(F2="",0,VLOOKUP(F2,L1:M7,2,FALSE)),IF(G2="",0,VLOOKUP(G2,L1:M7,2,FALSE)),IF(H2="",0,VLOOKUP(H2,L1:M7,2,FALSE)),IF(I2="",0,VLOOKUP(I2,L1:M7,2,FALSE)),IF(J2="",0,VLOOKUP(J2,L1:M7,2,FALSE)))
Cells with Data Validation
CellAllowCriteria
F2:J2List=$L$1:$L$7
Fantastic! Thank you so much for the quick and accurate reply!
 
Upvote 0
Fantastic! Thank you so much for the quick and accurate repl

added a second choice for how to get the total points. also to mention, both of these require a second column with the results giving the point each are worth
-------------
Using COUNTIF with a dropdown menu.xlsx
ABCDEFGHIJKLM
1winslossesrankwin%total pointsmatch 1match 2match 3match 4match 5fall 55
23175%14tech fall 5loss 0injury default 6decision 3tech fall 55
32250%12injury default 6loss 0injury default 6loss 0major decision 44
4decision 33
5injury default 66
6disqualification 66
7loss 00
Sheet1
Cell Formulas
RangeFormula
A2:A3A2=SUM(COUNTIF(F2:J2,L$1:L$6))
B2:B3B2=SUM(COUNTIF(F2:J2,L$7))
D2:D3D2=A2/SUM(A2:B2)
E2E2=SUM(IF(F2="",0,VLOOKUP(F2,L1:M7,2,FALSE)),IF(G2="",0,VLOOKUP(G2,L1:M7,2,FALSE)),IF(H2="",0,VLOOKUP(H2,L1:M7,2,FALSE)),IF(I2="",0,VLOOKUP(I2,L1:M7,2,FALSE)),IF(J2="",0,VLOOKUP(J2,L1:M7,2,FALSE)))
E3E3=SUM(SUMIFS(M1:M7,L1:L7,F3:J3))
Cells with Data Validation
CellAllowCriteria
F2:J3List=$L$1:$L$7
Thanks for the help! Do you know if there is a way to add another condition where Column E only totals points if in a different column it shows the correct condition? For example, if I added a column with a drop down menu to select a participant as scoring or non-scoring and column E only totals points when scoring is selected?
 
Upvote 0
Using COUNTIF with a dropdown menu.xlsx
ABCDEFGHIJKLMN
1winslossesrankwin%total pointsScoring?match 1match 2match 3match 4match 5fall 55
23175%14Scoringtech fall 5loss 0injury default 6decision 3tech fall 55
32250%No ScoreNot Scoringinjury default 6loss 0injury default 6loss 0major decision 44
4decision 33
5injury default 66
6disqualification 66
7loss 00
Sheet1
Cell Formulas
RangeFormula
A2:A3A2=SUM(COUNTIF(G2:K2,M$1:M$6))
B2:B3B2=SUM(COUNTIF(G2:K2,M$7))
D2:D3D2=A2/SUM(A2:B2)
E2E2=IF(F2="Scoring",SUM(IF(G2="",0,VLOOKUP(G2,M1:N7,2,FALSE)),IF(H2="",0,VLOOKUP(H2,M1:N7,2,FALSE)),IF(I2="",0,VLOOKUP(I2,M1:N7,2,FALSE)),IF(J2="",0,VLOOKUP(J2,M1:N7,2,FALSE)),IF(K2="",0,VLOOKUP(K2,M1:N7,2,FALSE))),"No Score")
E3E3=IF(F3="Scoring",SUM(SUMIFS(N1:N7,M1:M7,G3:K3)),"No Score")
Cells with Data Validation
CellAllowCriteria
F2:F3ListScoring, Not Scoring
G2:K3List=$M$1:$M$7
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

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