COUNTIF with multilayered dropdown selections

L

LWLD

New Member
Joined
Apr 22, 2022
Messages
10
Office Version
  1. 365
Platform
  1. Windows
Hi all,

I am a complete novice when it comes to Excel, I manage to do most things via Google searches and forums like this.

I have complied a sheet which has data values from 0 - 4 and managed to get a table to count up the number of each value grade (0 - 4). However, I am now trying to get this to allow me to filter these results via dropdown selections. I have included a minisheet to better explain/demonstrate this as I fear I will complicate it too much if I try. The 'Number of Marks at Grade' table is set up to count up the total across each donor value. I'd like to use the filter at the side to narrow the results more.

If anyone can assist i would be grateful, if not, thank you to those to took the time to read this.

Book1.ods
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAG
665Fired9mmPinchWashed1000000000
67Unwashed0010100000
68LoadWashed0000000001
69Unwashed1101010010
70.45sPinchWashed
71Unwashed
72LoadWashed
73Unwashed
74Unfired9mmPinchWashed1121121010
75Unwashed3434333323
76LoadWashed0101000010
77Unwashed1110012101
78.45sPinchWashed0121321111
79Unwashed3343433333
80LoadWashed1110011110
81Unwashed2211211221DonorNumber of Marks at GradeFilter
826Fired9mmPinchWashed000000000001234TotalCondition
83Unwashed0000000010162431410120Caliber
84LoadWashed010001001027640400120Contact
85Unwashed0011000100350432610120Surface
86.45sPinchWashed4725441826120
87Unwashed5474211164120
88LoadWashed61118100120
89Unwashed729463960120
90Unfired9mmPinchWashed0000000000853432400120
91Unwashed0000000000934571856120
92LoadWashed0000000000102035331319120
93Unwashed0000000001Total48938221460551200
94.45sPinchWashed0000000000%age41%32%18%5%5%
95Unwashed0000000000
96LoadWashed0000000000
97Unwashed0000000200
Gradings_(2)
Cell Formulas
RangeFormula
Y83Y83=COUNTIF(F2:O17,"0")
Z83Z83=COUNTIF(F2:O17,"1")
AA83AA83=COUNTIF(F2:O17,"2")
AB83AB83=COUNTIF(F2:O17,"3")
AC83AC83=COUNTIF(F2:O17,"4")
AD83:AD92AD83=SUM(Y83:AC83)
Y84Y84=COUNTIF(F18:O33,"0")
Z84Z84=COUNTIF(F18:O33,"1")
AA84AA84=COUNTIF(F18:O33,"2")
AB84AB84=COUNTIF(F18:O33,"3")
AC84AC84=COUNTIF(F18:O33,"4")
Y85Y85=COUNTIF(F34:O49,"0")
Z85Z85=COUNTIF(F34:O49,"1")
AA85AA85=COUNTIF(F34:O49,"2")
AB85AB85=COUNTIF(F34:O49,"3")
AC85AC85=COUNTIF(F34:O49,"4")
Y86Y86=COUNTIF(F50:O65,"0")
Z86Z86=COUNTIF(F50:O65,"1")
AA86AA86=COUNTIF(F50:O65,"2")
AB86AB86=COUNTIF(F50:O65,"3")
AC86AC86=COUNTIF(F50:O65,"4")
Y87Y87=COUNTIF(F66:O81,"0")
Z87Z87=COUNTIF(F66:O81,"1")
AA87AA87=COUNTIF(F66:O81,"2")
AB87AB87=COUNTIF(F66:O81,"3")
AC87AC87=COUNTIF(F66:O81,"4")
Y88Y88=COUNTIF(F82:O97,"0")
Z88Z88=COUNTIF(F82:O97,"1")
AA88AA88=COUNTIF(F82:O97,"2")
AB88AB88=COUNTIF(F82:O97,"3")
AC88AC88=COUNTIF(F82:O97,"4")
Y89Y89=COUNTIF(F98:O113,"0")
Z89Z89=COUNTIF(F98:O113,"1")
AA89AA89=COUNTIF(F98:O113,"2")
AB89AB89=COUNTIF(F98:O113,"3")
AC89AC89=COUNTIF(F98:O113,"4")
Y90Y90=COUNTIF(F114:O129,"0")
Z90Z90=COUNTIF(F114:O129,"1")
AA90AA90=COUNTIF(F114:O129,"2")
AB90AB90=COUNTIF(F114:O129,"3")
AC90AC90=COUNTIF(F114:O129,"4")
Y91Y91=COUNTIF(F130:O145,"0")
Z91Z91=COUNTIF(F130:O145,"1")
AA91AA91=COUNTIF(F130:O145,"2")
AB91AB91=COUNTIF(F130:O145,"3")
AC91AC91=COUNTIF(F130:O145,"4")
Y92Y92=COUNTIF(F146:O161,"0")
Z92Z92=COUNTIF(F146:O161,"1")
AA92AA92=COUNTIF(F146:O161,"2")
AB92AB92=COUNTIF(F146:O161,"3")
AC92AC92=COUNTIF(F146:O161,"4")
Y93:AC93Y93=SUM(Y83:Y92)
AD93AD93=IF(SUM(AD83:AD92)=SUM(Y93:AC93),(SUM(AD83:AD92)+SUM(Y93:AC93))/2,0)
Y94Y94=ROUND(Y93/AD93*100,0)&"%"
Z94Z94=ROUND(Z93/AD93*100,0)&"%"
AA94AA94=ROUND(AA93/AD93*100,0)&"%"
AB94AB94=ROUND(AB93/AD93*100,0)&"%"
AC94AC94=ROUND(AC93/AD93*100,0)&"%"
Cells with Data Validation
CellAllowCriteria
F66:O97List0,1,2,3,4
 
Sort by date Sort by votes
Hi @Eric W, I played around and it's working like a dream.

Cannot thank you enough.
 
Upvote 0

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
You must log in or register to reply here.

Similar threads

R
  • Question
VBA code to count under 5% and and non green cells
Replies
8
Views
375
footoo
F
RAJESH1960
  • Solved
Countifs formula maybe
Replies
7
Views
325
Eric W
Eric W
Sanjeev1976
  • Question
Pivot Sum at every change in price
Replies
1
Views
356
Tivakaran
T
H
  • Solved
Extract text from a string of text based on the range defined
Replies
9
Views
853
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,214,648
Messages
6,120,725
Members
448,987
Latest member
marion_davis

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