# Using COUNTIF with a dropdown menu

#### kcross31

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

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
11.3 KB · Views: 7

### Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

#### ExceLoki

##### Active Member
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

#### jasonb75

##### Well-known Member
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))``

#### ExceLoki

##### Active Member
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

#### Marcelo Branco

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

#### kcross31

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

#### kcross31

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

#### ExceLoki

##### Active Member
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

Replies
4
Views
92
Replies
1
Views
324
Replies
6
Views
468
Replies
8
Views
291
Replies
15
Views
919

1,190,701
Messages
5,982,383
Members
439,777
Latest member
daleEH

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