How to make Spillable: COUNTIFS,MINIFS, MAXIFS,PERCENTILE(IF.. Functions?

T

TheHack22

Board Regular
Joined
Feb 3, 2021
Messages
121
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
Hi Excel Experts,

I have this small project that I'm working on. I would like to make the formulas in Cells - D3:I3 spillable. These current formulas that I created are working fine, but the user of this MS Excel file/model, doesn't know much Excel and I'm trying to make the model as dynamic as possible.

Please see below the formulas contained in cell D3:I3. Columns C has a spillable "UNIQUE" Function.

D3=COUNTIFS(Plaintiff_AllData[Firm],C3,Plaintiff_AllData[Position],"Partner")
E3=MINIFS(Plaintiff_AllData[Rate],Plaintiff_AllData[Firm],$C3,Plaintiff_AllData[Position],"Partner")
F3=PERCENTILE(IF(Plaintiff_AllData[Firm]=$C3,IF(Plaintiff_AllData[Position]="Partner",IF(Plaintiff_AllData[Position]<>"",Plaintiff_AllData[Rate]))),0.25)
G3=PERCENTILE(IF(Plaintiff_AllData[Firm]=$C3,IF(Plaintiff_AllData[Position]="Partner",IF(Plaintiff_AllData[Position]<>"",Plaintiff_AllData[Rate]))),0.5)
H3=PERCENTILE(IF(Plaintiff_AllData[Firm]=$C3,IF(Plaintiff_AllData[Position]="Partner",IF(Plaintiff_AllData[Position]<>"",Plaintiff_AllData[Rate]))),0.75)
I3=MAXIFS(Plaintiff_AllData[Rate],Plaintiff_AllData[Firm],$C3,Plaintiff_AllData[Position],"Partner")

Is there a way to make the above spill? Please see the Screenshot for your reference.
1668272584483.png


1668272584483.png
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Sort by date Sort by votes
How about
Excel Formula: 
=COUNTIFS(Plaintiff_AllData[Firm],C3#,Plaintiff_AllData[Position],"Partner")
 
Upvote 0
Fluff said:
How about
Excel Formula: 
=COUNTIFS(Plaintiff_AllData[Firm],C3#,Plaintiff_AllData[Position],"Partner")
Click to expand...
@Fluff

Thanks very much for the quick and helpful response. The hash works perfectly for the COUNTIFS, MINIFS & MAXIFS FUNCTIONS.
How about the PERCENTILE(IF ..) FUNCTION? Can I make this a spillable function too? I'm getting a #NA error when I tried the hash with this one.

Imran
 
Upvote 0
Percentile is not a function I've used, can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
 
Upvote 0
Fluff said:
Percentile is not a function I've used, can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
Click to expand...
Hi @Fluff

Thanks again for your response. My apologies for the delayed response. I had some challenges installing the Xl2bb Add-in. This is my first time using it and I'm very thankful for your help with this as well.

Please see the below codes for the 2 mini sheets, please let me know if it works. These are all dummy data.
Sheet 1 - Output

Clients Analysis 11.12.2022.xlsx
ABCDEFGHI
1PositionCompaniesCountLow25th PercentileMedian75th PercentileHigh
2PartnersDynamicDynamicDynamicDynamicNot DynamicNot DynamicNot DynamicDynamic
31)Acme Corporation 15$38,250$42,750$43,650$49,500$54,000
42)Cupcake LLC3$34,875$41,063$47,250$49,500$51,750
53)Éclair Inc6$31,500$38,250$38,250$38,250$43,875
64)Grant & Eisenhoffer P.A.2$45,000$45,000$45,000$45,000$45,000
75)Home Furnishing23$22,950$38,250$41,400$43,875$58,500
86)Hooli8$32,625$36,281$41,400$45,000$46,125
97)Initech11$30,375$34,425$36,900$43,650$54,000
108)Umbrella Corporation17$37,350$39,600$44,775$49,500$60,750
Summary
Cell Formulas
RangeFormula
B3:B10B3=SEQUENCE(COUNTA(C3#))
C3:C10C3=UNIQUE(FILTER(Plaintiff_AllData[Firm],(Plaintiff_AllData[Position]="Partner")*(Plaintiff_AllData[Type]="Clients")))
D3:D10D3=COUNTIFS(Plaintiff_AllData[Firm],C3#,Plaintiff_AllData[Position],"Partner")
E3:E10E3=MINIFS(Plaintiff_AllData[Rate],Plaintiff_AllData[Firm],$C3#,Plaintiff_AllData[Position],"Partner")
F3:F10F3=PERCENTILE(IF(Plaintiff_AllData[Firm]=C3,IF(Plaintiff_AllData[Position]="Partner",IF(Plaintiff_AllData[Position]<>"",Plaintiff_AllData[Rate]))),0.25)
G3G3=PERCENTILE(IF(Plaintiff_AllData[Firm]=$C3,IF(Plaintiff_AllData[Position]="Partner",IF(Plaintiff_AllData[Position]<>"",Plaintiff_AllData[Rate]))),0.5)
H3H3=PERCENTILE(IF(Plaintiff_AllData[Firm]=$C3,IF(Plaintiff_AllData[Position]="Partner",IF(Plaintiff_AllData[Position]<>"",Plaintiff_AllData[Rate]))),0.75)
I3:I10I3=MAXIFS(Plaintiff_AllData[Rate],Plaintiff_AllData[Firm],C3#,Plaintiff_AllData[Position],"Partner")
G4:G10G4=IFERROR(PERCENTILE(IF(Plaintiff_AllData[Firm]=$C4,IF(Plaintiff_AllData[Position]="Partner",IF(Plaintiff_AllData[Position]<>"",Plaintiff_AllData[Rate]))),0.5),"Check Formula")
H4:H10H4=IFERROR(PERCENTILE(IF(Plaintiff_AllData[Firm]=$C4,IF(Plaintiff_AllData[Position]="Partner",IF(Plaintiff_AllData[Position]<>"",Plaintiff_AllData[Rate]))),0.75),"Check Formula")
Press CTRL+SHIFT+ENTER to enter array formulas.
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C4:C12Cell ValueduplicatestextNO




Sheet 2 -Data

Clients Analysis 11.12.2022.xlsx
ABCDE
1RateGraded PositionPositionTypeFirm
2$24,750Senior AssociateAssociateClientsAce Store
3$18,450Senior AssociateAssociateClientsAce Store
4$26,100Senior AssociateAssociateClientsAce Store
5$21,375Senior AssociateAssociateClientsAce Store
6$28,125Senior AssociateAssociateClientsAce Store
7$24,975Financial AnalystFinancial AnalystClientsAce Store
8$26,775InvestigatorInvestigatorClientsAce Store
9$26,775InvestigatorInvestigatorClientsAce Store
10$18,000InvestigatorInvestigatorClientsAce Store
11$37,125Of CounselOf CounselClientsAce Store
12$18,900Staff AttorneyStaff AttorneyClientsAce Store
13$18,900Staff AttorneyStaff AttorneyClientsAce Store
14$18,000Junior AssociateAssociateClientsAcme Corporation
15$19,125Midlevel AssociateAssociateClientsAcme Corporation
16$16,875ParalegalParalegalClientsAcme Corporation
17$14,625ParalegalParalegalClientsAcme Corporation
18$15,750ParalegalParalegalClientsAcme Corporation
19$14,625ParalegalParalegalClientsAcme Corporation
20$13,500ParalegalParalegalClientsAcme Corporation
21$18,000ParalegalParalegalClientsAcme Corporation
22$42,750Senior PartnerPartnerClientsAcme Corporation
23$45,000Senior PartnerPartnerClientsAcme Corporation
24$54,000Senior PartnerPartnerClientsAcme Corporation
25$43,650Senior PartnerPartnerClientsAcme Corporation
26$49,500Senior PartnerPartnerClientsAcme Corporation
27$45,000Senior PartnerPartnerClientsAcme Corporation
28$42,750Senior PartnerPartnerClientsAcme Corporation
29$43,650Senior PartnerPartnerClientsAcme Corporation
30$49,500Senior PartnerPartnerClientsAcme Corporation
31$49,500Senior PartnerPartnerClientsAcme Corporation
32$38,250Senior PartnerPartnerClientsAcme Corporation
33$54,000Senior PartnerPartnerClientsAcme Corporation
34$40,500Senior PartnerPartnerClientsAcme Corporation
35$40,500Senior PartnerPartnerClientsAcme Corporation
36$42,750Senior PartnerPartnerClientsAcme Corporation
37$18,000Staff AttorneyStaff AttorneyClientsAcme Corporation
38$19,125Staff AttorneyStaff AttorneyClientsAcme Corporation
39$18,000Staff AttorneyStaff AttorneyClientsAcme Corporation
40$18,000Staff AttorneyStaff AttorneyClientsAcme Corporation
41$19,125Staff AttorneyStaff AttorneyClientsAcme Corporation
42$18,000Staff AttorneyStaff AttorneyClientsAcme Corporation
43$13,050ParalegalParalegalClientsCupcake LLC
44$15,075ParalegalParalegalClientsCupcake LLC
45$13,950ParalegalParalegalClientsCupcake LLC
46$14,625ParalegalParalegalClientsCupcake LLC
47$34,875Senior PartnerPartnerClientsCupcake LLC
48$47,250Senior PartnerPartnerClientsCupcake LLC
49$51,750Senior PartnerPartnerClientsCupcake LLC
50$19,125Midlevel AssociateAssociateClientsÉclair Inc
51$28,125Senior AssociateAssociateClientsÉclair Inc
52$22,500Senior AssociateAssociateClientsÉclair Inc
53$27,000Senior AssociateAssociateClientsÉclair Inc
54$13,275ParalegalParalegalClientsÉclair Inc
55$13,275ParalegalParalegalClientsÉclair Inc
56$13,950ParalegalParalegalClientsÉclair Inc
57$31,500Midlevel PartnerPartnerClientsÉclair Inc
58$43,875Senior PartnerPartnerClientsÉclair Inc
59$38,250Senior PartnerPartnerClientsÉclair Inc
60$38,250Senior PartnerPartnerClientsÉclair Inc
61$38,250Senior PartnerPartnerClientsÉclair Inc
62$38,250Senior PartnerPartnerClientsÉclair Inc
63$16,425Junior AssociateAssociateClientsGrant & Eisenhoffer P.A.
64$16,425Junior AssociateAssociateClientsGrant & Eisenhoffer P.A.
65$33,750Senior AssociateAssociateClientsGrant & Eisenhoffer P.A.
66$20,250Senior AssociateAssociateClientsGrant & Eisenhoffer P.A.
67$9,450ParalegalParalegalClientsGrant & Eisenhoffer P.A.
68$9,900ParalegalParalegalClientsGrant & Eisenhoffer P.A.
69$45,000Senior PartnerPartnerClientsGrant & Eisenhoffer P.A.
70$45,000Senior PartnerPartnerClientsGrant & Eisenhoffer P.A.
71$24,750Senior AssociateAssociateClientsGlobex Corporation
72$13,725ParalegalParalegalClientsGlobex Corporation
73$12,375ParalegalParalegalClientsGlobex Corporation
74$11,475ParalegalParalegalClientsGlobex Corporation
75$10,125ParalegalParalegalClientsGlobex Corporation
76$12,375ParalegalParalegalClientsGlobex Corporation
77$11,700ParalegalParalegalClientsGlobex Corporation
78$10,800ParalegalParalegalClientsHome Furnishing
79$10,125ParalegalParalegalClientsHome Furnishing
80$13,725ParalegalParalegalClientsHome Furnishing
81$10,125ParalegalParalegalClientsHome Furnishing
82$35,100Midlevel PartnerPartnerClientsHome Furnishing
83$35,100Midlevel PartnerPartnerClientsHome Furnishing
84$22,950Midlevel PartnerPartnerClientsHome Furnishing
85$38,250Senior PartnerPartnerClientsHome Furnishing
86$35,100Senior PartnerPartnerClientsHome Furnishing
87$38,250Senior PartnerPartnerClientsHome Furnishing
88$36,900Senior PartnerPartnerClientsHome Furnishing
89$41,400Senior PartnerPartnerClientsHome Furnishing
90$38,250Senior PartnerPartnerClientsHome Furnishing
91$41,400Senior PartnerPartnerClientsHome Furnishing
92$38,250Senior PartnerPartnerClientsHome Furnishing
93$45,000Senior PartnerPartnerClientsHome Furnishing
94$38,250Senior PartnerPartnerClientsHome Furnishing
95$41,400Senior PartnerPartnerClientsHome Furnishing
96$47,250Senior PartnerPartnerClientsHome Furnishing
97$56,250Senior PartnerPartnerClientsHome Furnishing
98$43,875Senior PartnerPartnerClientsHome Furnishing
99$43,875Senior PartnerPartnerClientsHome Furnishing
100$56,250Senior PartnerPartnerClientsHome Furnishing
101$58,500Senior PartnerPartnerClientsHome Furnishing
102$39,375Senior PartnerPartnerClientsHome Furnishing
103$41,625Senior PartnerPartnerClientsHome Furnishing
104$43,875Senior PartnerPartnerClientsHome Furnishing
105$18,225Staff AttorneyStaff AttorneyClientsHome Furnishing
106$17,775Staff AttorneyStaff AttorneyClientsHome Furnishing
107$19,125Staff AttorneyStaff AttorneyClientsHome Furnishing
108$19,125Staff AttorneyStaff AttorneyClientsHome Furnishing
109$15,750Staff AttorneyStaff AttorneyClientsHome Furnishing
110$20,250Staff AttorneyStaff AttorneyClientsHome Furnishing
111$15,750Staff AttorneyStaff AttorneyClientsHome Furnishing
112$15,750Staff AttorneyStaff AttorneyClientsHome Furnishing
113$16,875Staff AttorneyStaff AttorneyClientsHome Furnishing
114$18,225Staff AttorneyStaff AttorneyClientsHome Furnishing
115$18,225Staff AttorneyStaff AttorneyClientsHome Furnishing
116$18,000Staff AttorneyStaff AttorneyClientsHome Furnishing
117$19,125Staff AttorneyStaff AttorneyClientsHome Furnishing
118$19,125Staff AttorneyStaff AttorneyClientsHome Furnishing
119$14,175Staff AttorneyStaff AttorneyClientsHome Furnishing
120$15,750Staff AttorneyStaff AttorneyClientsHome Furnishing
121$19,125Staff AttorneyStaff AttorneyClientsHome Furnishing
122$20,250Staff AttorneyStaff AttorneyClientsHome Furnishing
123$20,250Junior AssociateAssociateClientsHooli
124$20,250Junior AssociateAssociateClientsHooli
125$20,250Junior AssociateAssociateClientsHooli
126$20,250Junior AssociateAssociateClientsHooli
127$16,875Junior AssociateAssociateClientsHooli
128$20,250Junior AssociateAssociateClientsHooli
129$20,250Junior AssociateAssociateClientsHooli
130$20,250Junior AssociateAssociateClientsHooli
131$18,000Midlevel AssociateAssociateClientsHooli
132$20,250Midlevel AssociateAssociateClientsHooli
133$18,675Midlevel AssociateAssociateClientsHooli
134$23,175Midlevel AssociateAssociateClientsHooli
135$19,575Midlevel AssociateAssociateClientsHooli
136$18,675Midlevel AssociateAssociateClientsHooli
137$33,750Senior AssociateAssociateClientsHooli
138$20,250Senior AssociateAssociateClientsHooli
139$15,750Senior AssociateAssociateClientsHooli
140$28,350Senior AssociateAssociateClientsHooli
141$23,175Senior AssociateAssociateClientsHooli
142$21,375Senior AssociateAssociateClientsHooli
143$20,250Senior AssociateAssociateClientsHooli
144$20,700Senior AssociateAssociateClientsHooli
145$20,250Senior AssociateAssociateClientsHooli
146$19,125Senior AssociateAssociateClientsHooli
147$21,600Senior AssociateAssociateClientsHooli
148$21,825Senior AssociateAssociateClientsHooli
149$20,250Senior AssociateAssociateClientsHooli
150$14,400ParalegalParalegalClientsHooli
151$35,100Junior PartnerPartnerClientsHooli
152$41,400Senior PartnerPartnerClientsHooli
153$45,000Senior PartnerPartnerClientsHooli
154$45,000Senior PartnerPartnerClientsHooli
155$32,625Senior PartnerPartnerClientsHooli
156$46,125Senior PartnerPartnerClientsHooli
157$41,400Senior PartnerPartnerClientsHooli
158$36,675Senior PartnerPartnerClientsHooli
159$20,700Staff AttorneyStaff AttorneyClientsHooli
160$25,875Junior AssociateAssociateClientsInitech
161$18,000Junior AssociateAssociateClientsInitech
162$16,875Junior AssociateAssociateClientsInitech
163$37,800Of CounselOf CounselClientsInitech
164$24,300Of CounselOf CounselClientsInitech
165$49,050Of CounselOf CounselClientsInitech
166$12,375ParalegalParalegalClientsInitech
167$13,050ParalegalParalegalClientsInitech
168$12,375ParalegalParalegalClientsInitech
169$34,650Junior PartnerPartnerClientsInitech
170$34,200Junior PartnerPartnerClientsInitech
171$30,375Junior PartnerPartnerClientsInitech
172$34,200Junior PartnerPartnerClientsInitech
173$35,100Midlevel PartnerPartnerClientsInitech
174$37,800Senior PartnerPartnerClientsInitech
175$36,900Senior PartnerPartnerClientsInitech
176$43,650Senior PartnerPartnerClientsInitech
177$54,000Senior PartnerPartnerClientsInitech
178$43,650Senior PartnerPartnerClientsInitech
179$49,500Senior PartnerPartnerClientsInitech
180$46,125Senior PartnerPartnerClientsUmbrella Corporation
181$48,600Senior PartnerPartnerClientsUmbrella Corporation
182$44,775Senior PartnerPartnerClientsUmbrella Corporation
183$51,750Senior PartnerPartnerClientsUmbrella Corporation
184$42,750Senior PartnerPartnerClientsUmbrella Corporation
185$37,800Senior PartnerPartnerClientsUmbrella Corporation
186$39,150Senior PartnerPartnerClientsUmbrella Corporation
187$51,750Senior PartnerPartnerClientsUmbrella Corporation
188$49,500Senior PartnerPartnerClientsUmbrella Corporation
189$40,275Senior PartnerPartnerClientsUmbrella Corporation
190$60,750Senior PartnerPartnerClientsUmbrella Corporation
191$41,400Senior PartnerPartnerClientsUmbrella Corporation
192$59,625Senior PartnerPartnerClientsUmbrella Corporation
193$39,600Senior PartnerPartnerClientsUmbrella Corporation
194$37,800Senior PartnerPartnerClientsUmbrella Corporation
195$37,350Senior PartnerPartnerClientsUmbrella Corporation
196$44,775Senior PartnerPartnerClientsUmbrella Corporation
197$19,125Staff AttorneyStaff AttorneyClientsUmbrella Corporation
198$19,125Staff AttorneyStaff AttorneyClientsUmbrella Corporation
199$19,125Staff AttorneyStaff AttorneyClientsUmbrella Corporation
200$19,125Staff AttorneyStaff AttorneyClientsUmbrella Corporation
201$20,025Staff AttorneyStaff AttorneyClientsUmbrella Corporation
202$19,125Staff AttorneyStaff AttorneyClientsUmbrella Corporation
Plaintiff All RawData-Combined
 
Last edited:
Upvote 0
Thanks for that, how about
Excel Formula: 
=BYROW(C3#,LAMBDA(br,PERCENTILE(IF((Plaintiff_AllData[Firm]=br)*(Plaintiff_AllData[Position]="Partner")*(Plaintiff_AllData[Position]<>""),Plaintiff_AllData[Rate]),0.25)))
 
Upvote 0
Solution
Fluff said:
Thanks for that, how about
Excel Formula: 
=BYROW(C3#,LAMBDA(br,PERCENTILE(IF((Plaintiff_AllData[Firm]=br)*(Plaintiff_AllData[Position]="Partner")*(Plaintiff_AllData[Position]<>""),Plaintiff_AllData[Rate]),0.25)))
Click to expand...
@Fluff

Thanks so much. I've never used BYROW and LAMDA Functions. I thought this was not possible. You're such a live savior. :)
Imran
 
Upvote 0
Glad to help & thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Solved
MS Excel COUNTIFS, MAXIFS, MINIFS, AVERAGEIFS with OR condition
Replies
5
Views
782
Peter_SSs
Peter_SSs
T
  • Question Question
Add another criteria to Excel Nested FILTER (Spillable) Functions
Replies
2
Views
626
TheHack22
T
T
  • Question Question
DAX- Power Pivot - How to show the % difference between values in a PVT
Replies
0
Views
360
TheHack22
T
R
  • Solved
Is it possible to make TYPE and CELL functions to work on a selected range?
2
Replies
13
Views
983
smozgur
smozgur
M
  • Solved
Spill formula with COUNTIF
Replies
4
Views
906
Fluff
Fluff

Forum statistics

Threads
1,223,885
Messages
6,175,183
Members
452,615
Latest member
bogeys2birdies

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