MS Excel COUNTIFS, MAXIFS, MINIFS, AVERAGEIFS with OR condition

TheHack22

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

Hope everyone is well. I have the following question on a few spillable formulas in MS Excel for a project I'm working on, which I would like to be dynamic.

These are the formulas I have:

1)C3=UNIQUE(FILTER(Plaintiff_AllData[Firm],(Plaintiff_AllData[Position]="Partner")*(Plaintiff_AllData[Type]="Clients")))
2)D3=COUNTIFS(Plaintiff_AllData[Firm],C3#,Plaintiff_AllData[Position],"Partner",Plaintiff_AllData[Type],"Clients")
3)E3=MINIFS(Plaintiff_AllData[Rate],Plaintiff_AllData[Firm],$C3#,Plaintiff_AllData[Position],"Partner",Plaintiff_AllData[Type],"Clients")
4)F3 =MAXIFS(Plaintiff_AllData[Rate],Plaintiff_AllData[Firm],$C3#,Plaintiff_AllData[Position],"Partner",Plaintiff_AllData[Type],"Clients")
5)G3=AVERAGEIFS(Plaintiff_AllData[Rate],Plaintiff_AllData[Position],"Partner",Plaintiff_AllData[Firm],C3#,Plaintiff_AllData[Type],"Clients")

Basically, I needed an OR Condition, the OR condition needs to look in the same column (Column name - "Type" - in the mini sheet attached).
So I needed the criteria to be "Clients or Clients A".

Is this possible? I'm trying to main the formulas to be spillable

Imran


This is the Dashboard:


Clients Analysis 11.12.2022.xlsx
ABCDEFG
1PositionSeq#CompaniesCountLowHighAverage
2Partners
31)Acme Corporation 10$38,250$54,000$45,090
42)Cupcake LLC3$34,875$51,750$44,625
53)Éclair Inc6$31,500$43,875$38,063
64)Grant & Eisenhoffer P.A.2$45,000$45,000$45,000
75)Home Furnishing23$22,950$58,500$41,586
86)Hooli8$32,625$46,125$40,416
97)Initech8$34,200$54,000$41,850
108)Umbrella Corporation11$37,350$60,750$46,268
Summary B
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",Plaintiff_AllData[Type],"Clients")
E3:E10E3=MINIFS(Plaintiff_AllData[Rate],Plaintiff_AllData[Firm],$C3#,Plaintiff_AllData[Position],"Partner",Plaintiff_AllData[Type],"Clients")
F3:F10F3=MAXIFS(Plaintiff_AllData[Rate],Plaintiff_AllData[Firm],$C3#,Plaintiff_AllData[Position],"Partner",Plaintiff_AllData[Type],"Clients")
G3:G10G3=AVERAGEIFS(Plaintiff_AllData[Rate],Plaintiff_AllData[Position],"Partner",Plaintiff_AllData[Firm],C3#,Plaintiff_AllData[Type],"Clients")
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C4:C12Cell ValueduplicatestextNO


This is the underlying dataset(Dummy).

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 AssociatePartnerClients AAce Store
7$24,975Financial AnalystFinancial AnalystClientsAce Store
8$26,775InvestigatorPartnerClients AAce Store
9$26,775InvestigatorInvestigatorClientsAce Store
10$18,000InvestigatorInvestigatorClientsAce Store
11$37,125Of CounselOf CounselClientsAce Store
12$18,900Staff AttorneyPartnerClients AAce Store
13$18,900Staff AttorneyStaff AttorneyClientsAce Store
14$18,000Junior AssociatePartnerClients AAcme Corporation
15$19,125Midlevel AssociatePartnerClients AAcme Corporation
16$16,875ParalegalParalegalClientsAcme Corporation
17$14,625ParalegalParalegalClientsAcme Corporation
18$15,750ParalegalPartnerClients AAcme Corporation
19$14,625ParalegalPartnerClients AAcme 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 PartnerPartnerClients AAcme Corporation
27$45,000Senior PartnerPartnerClients AAcme Corporation
28$42,750Senior PartnerPartnerClients AAcme Corporation
29$43,650Senior PartnerPartnerClients AAcme Corporation
30$49,500Senior PartnerPartnerClients AAcme 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 AttorneyPartnerClients AAcme Corporation
42$18,000Staff AttorneyPartnerClients AAcme Corporation
43$13,050ParalegalPartnerClients ACupcake LLC
44$15,075ParalegalPartnerClients ACupcake 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 AssociatePartnerClients AÉclair Inc
52$22,500Senior AssociatePartnerClients AÉclair Inc
53$27,000Senior AssociatePartnerClients AÉ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 AssociatePartnerClients AGrant & Eisenhoffer P.A.
66$20,250Senior AssociatePartnerClients AGrant & 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,475ParalegalPartnerClients AGlobex Corporation
75$10,125ParalegalPartnerClients AGlobex Corporation
76$12,375ParalegalPartnerClients AGlobex Corporation
77$11,700ParalegalPartnerClients AGlobex Corporation
78$10,800ParalegalPartnerClients AHome 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 PartnerPartnerClients BHome Furnishing
99$43,875Senior PartnerPartnerClients BHome Furnishing
100$56,250Senior PartnerPartnerClients BHome Furnishing
101$58,500Senior PartnerPartnerClients BHome 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 AttorneyClients BHome Furnishing
113$16,875Staff AttorneyStaff AttorneyClients BHome Furnishing
114$18,225Staff AttorneyStaff AttorneyClients BHome 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 AssociatePartnerClients AHooli
127$16,875Junior AssociatePartnerClients AHooli
128$20,250Junior AssociatePartnerClients AHooli
129$20,250Junior AssociatePartnerClients AHooli
130$20,250Junior AssociatePartnerClients AHooli
131$18,000Midlevel AssociatePartnerClients AHooli
132$20,250Midlevel AssociatePartnerClients AHooli
133$18,675Midlevel AssociatePartnerClients AHooli
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 PartnerPartnerClients BHooli
153$45,000Senior PartnerPartnerClients BHooli
154$45,000Senior PartnerPartnerClients BHooli
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 CounselPartnerClients AInitech
165$49,050Of CounselPartnerClients AInitech
166$12,375ParalegalPartnerClients AInitech
167$13,050ParalegalPartnerClients AInitech
168$12,375ParalegalPartnerClients AInitech
169$34,650Junior PartnerPartnerClients AInitech
170$34,200Junior PartnerPartnerClients AInitech
171$30,375Junior PartnerPartnerClients AInitech
172$34,200Junior PartnerPartnerClientsInitech
173$35,100Midlevel PartnerPartnerClientsInitech
174$37,800Senior PartnerPartnerClientsInitech
175$36,900Senior PartnerPartnerClientsInitech
176$43,650Senior PartnerPartnerClients BInitech
177$54,000Senior PartnerPartnerClientsInitech
178$43,650Senior PartnerPartnerClientsInitech
179$49,500Senior PartnerPartnerClientsInitech
180$46,125Senior PartnerPartnerClientsUmbrella Corporation
181$48,600Senior PartnerPartnerClients AUmbrella Corporation
182$44,775Senior PartnerPartnerClients AUmbrella Corporation
183$51,750Senior PartnerPartnerClients AUmbrella Corporation
184$42,750Senior PartnerPartnerClients AUmbrella Corporation
185$37,800Senior PartnerPartnerClients AUmbrella Corporation
186$39,150Senior PartnerPartnerClients AUmbrella Corporation
187$51,750Senior PartnerPartnerClientsUmbrella Corporation
188$49,500Senior PartnerPartnerClientsUmbrella Corporation
189$40,275Senior PartnerPartnerClientsUmbrella Corporation
190$60,750Senior PartnerPartnerClients BUmbrella Corporation
191$41,400Senior PartnerPartnerClients BUmbrella Corporation
192$59,625Senior PartnerPartnerClients BUmbrella Corporation
193$39,600Senior PartnerPartnerClients BUmbrella 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
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
So I needed the criteria to be "Clients or Clients A".
Try like this
=UNIQUE(FILTER(Plaintiff_AllData[Firm],(Plaintiff_AllData[Position]="Partner")*((Plaintiff_AllData[Type]="Clients")+(Plaintiff_AllData[Type]="Clients A"))))
 
Upvote 0
Try like this
=UNIQUE(FILTER(Plaintiff_AllData[Firm],(Plaintiff_AllData[Position]="Partner")*((Plaintiff_AllData[Type]="Clients")+(Plaintiff_AllData[Type]="Clients A"))))
Hi @Peter_SSs

Thanks very much. This works well. How about the IFS function, is it possible to achieve the same ("Clients" + "Clients A") with these too?
Imran
 
Upvote 0
How about the IFS function, is it possible to achieve the same ("Clients" + "Clients A") with these too?
Sorry, got called away before I had time to look at the others properly.
No, if you want the formulas to spill then something different will be needed.

I have also included a slight variation for the Companies column below as this would allow simpler expansion if you wanted to include more items in your 'OR' condition.
(Also included a slight variation for Seq# as it is a couple of characters shorter ;))

See how these go.

TheHack22.xlsm
BCDEFG
1Seq#CompaniesCountLowHighAverage
2
31Ace Store3189002812524600
42Acme Corporation 21146255400037425
53Cupcake LLC5130505175032400
64Éclair Inc9225004387534000
75Grant & Eisenhoffer P.A.4202504500036000
86Globex Corporation4101251237511418.75
97Home Furnishing20108005625038238.75
108Hooli13168754612526671.1538
119Initech15123755400033435
1210Umbrella Corporation13373505175044030.7692
Sheet1
Cell Formulas
RangeFormula
B3:B12B3=SEQUENCE(ROWS(C3#))
C3:C12C3=UNIQUE(FILTER(Plaintiff_AllData[Firm],(Plaintiff_AllData[Position]="Partner")*(ISNUMBER(MATCH(Plaintiff_AllData[Type],{"Clients","Clients A"},0)))))
D3:D12D3=BYROW(C3#,LAMBDA(Co,COUNT(FILTER(Plaintiff_AllData[Rate],(Plaintiff_AllData[Firm]=Co)*(Plaintiff_AllData[Position]="Partner")*(ISNUMBER(MATCH(Plaintiff_AllData[Type],{"Clients","Clients A"},0)))))))
E3:E12E3=BYROW(C3#,LAMBDA(Co,AGGREGATE(15,6,Plaintiff_AllData[Rate]/((Plaintiff_AllData[Firm]=Co)*(Plaintiff_AllData[Position]="Partner")*(ISNUMBER(MATCH(Plaintiff_AllData[Type],{"Clients","Clients A"},0)))),1)))
F3:F12F3=BYROW(C3#,LAMBDA(Co,AGGREGATE(14,6,Plaintiff_AllData[Rate]/((Plaintiff_AllData[Firm]=Co)*(Plaintiff_AllData[Position]="Partner")*(ISNUMBER(MATCH(Plaintiff_AllData[Type],{"Clients","Clients A"},0)))),1)))
G3:G12G3=BYROW(C3#,LAMBDA(Co,AVERAGE(FILTER(Plaintiff_AllData[Rate],(Plaintiff_AllData[Firm]=Co)*(Plaintiff_AllData[Position]="Partner")*(ISNUMBER(MATCH(Plaintiff_AllData[Type],{"Clients","Clients A"},0))),NA()))))
Dynamic array formulas.
 
Upvote 0
Solution
Sorry, got called away before I had time to look at the others properly.
No, if you want the formulas to spill then something different will be needed.

I have also included a slight variation for the Companies column below as this would allow simpler expansion if you wanted to include more items in your 'OR' condition.
(Also included a slight variation for Seq# as it is a couple of characters shorter ;))

See how these go.

TheHack22.xlsm
BCDEFG
1Seq#CompaniesCountLowHighAverage
2
31Ace Store3189002812524600
42Acme Corporation 21146255400037425
53Cupcake LLC5130505175032400
64Éclair Inc9225004387534000
75Grant & Eisenhoffer P.A.4202504500036000
86Globex Corporation4101251237511418.75
97Home Furnishing20108005625038238.75
108Hooli13168754612526671.1538
119Initech15123755400033435
1210Umbrella Corporation13373505175044030.7692
Sheet1
Cell Formulas
RangeFormula
B3:B12B3=SEQUENCE(ROWS(C3#))
C3:C12C3=UNIQUE(FILTER(Plaintiff_AllData[Firm],(Plaintiff_AllData[Position]="Partner")*(ISNUMBER(MATCH(Plaintiff_AllData[Type],{"Clients","Clients A"},0)))))
D3:D12D3=BYROW(C3#,LAMBDA(Co,COUNT(FILTER(Plaintiff_AllData[Rate],(Plaintiff_AllData[Firm]=Co)*(Plaintiff_AllData[Position]="Partner")*(ISNUMBER(MATCH(Plaintiff_AllData[Type],{"Clients","Clients A"},0)))))))
E3:E12E3=BYROW(C3#,LAMBDA(Co,AGGREGATE(15,6,Plaintiff_AllData[Rate]/((Plaintiff_AllData[Firm]=Co)*(Plaintiff_AllData[Position]="Partner")*(ISNUMBER(MATCH(Plaintiff_AllData[Type],{"Clients","Clients A"},0)))),1)))
F3:F12F3=BYROW(C3#,LAMBDA(Co,AGGREGATE(14,6,Plaintiff_AllData[Rate]/((Plaintiff_AllData[Firm]=Co)*(Plaintiff_AllData[Position]="Partner")*(ISNUMBER(MATCH(Plaintiff_AllData[Type],{"Clients","Clients A"},0)))),1)))
G3:G12G3=BYROW(C3#,LAMBDA(Co,AVERAGE(FILTER(Plaintiff_AllData[Rate],(Plaintiff_AllData[Firm]=Co)*(Plaintiff_AllData[Position]="Partner")*(ISNUMBER(MATCH(Plaintiff_AllData[Type],{"Clients","Clients A"},0))),NA()))))
Dynamic array formulas.
Hi @Peter_SSs

Sorry for the late response, my bed called me. :) This is just incredible, you helped preserve my sanity 😂. I was googling like crazy and didn't come across anything that was close to being helpful. Thanks a million, you're the best.
Imran
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

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