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:
This is the underlying dataset(Dummy).
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 | |||||||||
---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | |||
1 | Position | Seq# | Companies | Count | Low | High | Average | ||
2 | Partners | ||||||||
3 | 1) | Acme Corporation | 10 | $38,250 | $54,000 | $45,090 | |||
4 | 2) | Cupcake LLC | 3 | $34,875 | $51,750 | $44,625 | |||
5 | 3) | Éclair Inc | 6 | $31,500 | $43,875 | $38,063 | |||
6 | 4) | Grant & Eisenhoffer P.A. | 2 | $45,000 | $45,000 | $45,000 | |||
7 | 5) | Home Furnishing | 23 | $22,950 | $58,500 | $41,586 | |||
8 | 6) | Hooli | 8 | $32,625 | $46,125 | $40,416 | |||
9 | 7) | Initech | 8 | $34,200 | $54,000 | $41,850 | |||
10 | 8) | Umbrella Corporation | 11 | $37,350 | $60,750 | $46,268 | |||
Summary B |
Cell Formulas | ||
---|---|---|
Range | Formula | |
B3:B10 | B3 | =SEQUENCE(COUNTA(C3#)) |
C3:C10 | C3 | =UNIQUE(FILTER(Plaintiff_AllData[Firm],(Plaintiff_AllData[Position]="Partner")*(Plaintiff_AllData[Type]="Clients"))) |
D3:D10 | D3 | =COUNTIFS(Plaintiff_AllData[Firm],C3#,Plaintiff_AllData[Position],"Partner",Plaintiff_AllData[Type],"Clients") |
E3:E10 | E3 | =MINIFS(Plaintiff_AllData[Rate],Plaintiff_AllData[Firm],$C3#,Plaintiff_AllData[Position],"Partner",Plaintiff_AllData[Type],"Clients") |
F3:F10 | F3 | =MAXIFS(Plaintiff_AllData[Rate],Plaintiff_AllData[Firm],$C3#,Plaintiff_AllData[Position],"Partner",Plaintiff_AllData[Type],"Clients") |
G3:G10 | G3 | =AVERAGEIFS(Plaintiff_AllData[Rate],Plaintiff_AllData[Position],"Partner",Plaintiff_AllData[Firm],C3#,Plaintiff_AllData[Type],"Clients") |
Dynamic array formulas. |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
C4:C12 | Cell Value | duplicates | text | NO |
This is the underlying dataset(Dummy).
Clients Analysis 11.12.2022.xlsx | |||||||
---|---|---|---|---|---|---|---|
A | B | C | D | E | |||
1 | Rate | Graded Position | Position | Type | Firm | ||
2 | $24,750 | Senior Associate | Associate | Clients | Ace Store | ||
3 | $18,450 | Senior Associate | Associate | Clients | Ace Store | ||
4 | $26,100 | Senior Associate | Associate | Clients | Ace Store | ||
5 | $21,375 | Senior Associate | Associate | Clients | Ace Store | ||
6 | $28,125 | Senior Associate | Partner | Clients A | Ace Store | ||
7 | $24,975 | Financial Analyst | Financial Analyst | Clients | Ace Store | ||
8 | $26,775 | Investigator | Partner | Clients A | Ace Store | ||
9 | $26,775 | Investigator | Investigator | Clients | Ace Store | ||
10 | $18,000 | Investigator | Investigator | Clients | Ace Store | ||
11 | $37,125 | Of Counsel | Of Counsel | Clients | Ace Store | ||
12 | $18,900 | Staff Attorney | Partner | Clients A | Ace Store | ||
13 | $18,900 | Staff Attorney | Staff Attorney | Clients | Ace Store | ||
14 | $18,000 | Junior Associate | Partner | Clients A | Acme Corporation | ||
15 | $19,125 | Midlevel Associate | Partner | Clients A | Acme Corporation | ||
16 | $16,875 | Paralegal | Paralegal | Clients | Acme Corporation | ||
17 | $14,625 | Paralegal | Paralegal | Clients | Acme Corporation | ||
18 | $15,750 | Paralegal | Partner | Clients A | Acme Corporation | ||
19 | $14,625 | Paralegal | Partner | Clients A | Acme Corporation | ||
20 | $13,500 | Paralegal | Paralegal | Clients | Acme Corporation | ||
21 | $18,000 | Paralegal | Paralegal | Clients | Acme Corporation | ||
22 | $42,750 | Senior Partner | Partner | Clients | Acme Corporation | ||
23 | $45,000 | Senior Partner | Partner | Clients | Acme Corporation | ||
24 | $54,000 | Senior Partner | Partner | Clients | Acme Corporation | ||
25 | $43,650 | Senior Partner | Partner | Clients | Acme Corporation | ||
26 | $49,500 | Senior Partner | Partner | Clients A | Acme Corporation | ||
27 | $45,000 | Senior Partner | Partner | Clients A | Acme Corporation | ||
28 | $42,750 | Senior Partner | Partner | Clients A | Acme Corporation | ||
29 | $43,650 | Senior Partner | Partner | Clients A | Acme Corporation | ||
30 | $49,500 | Senior Partner | Partner | Clients A | Acme Corporation | ||
31 | $49,500 | Senior Partner | Partner | Clients | Acme Corporation | ||
32 | $38,250 | Senior Partner | Partner | Clients | Acme Corporation | ||
33 | $54,000 | Senior Partner | Partner | Clients | Acme Corporation | ||
34 | $40,500 | Senior Partner | Partner | Clients | Acme Corporation | ||
35 | $40,500 | Senior Partner | Partner | Clients | Acme Corporation | ||
36 | $42,750 | Senior Partner | Partner | Clients | Acme Corporation | ||
37 | $18,000 | Staff Attorney | Staff Attorney | Clients | Acme Corporation | ||
38 | $19,125 | Staff Attorney | Staff Attorney | Clients | Acme Corporation | ||
39 | $18,000 | Staff Attorney | Staff Attorney | Clients | Acme Corporation | ||
40 | $18,000 | Staff Attorney | Staff Attorney | Clients | Acme Corporation | ||
41 | $19,125 | Staff Attorney | Partner | Clients A | Acme Corporation | ||
42 | $18,000 | Staff Attorney | Partner | Clients A | Acme Corporation | ||
43 | $13,050 | Paralegal | Partner | Clients A | Cupcake LLC | ||
44 | $15,075 | Paralegal | Partner | Clients A | Cupcake LLC | ||
45 | $13,950 | Paralegal | Paralegal | Clients | Cupcake LLC | ||
46 | $14,625 | Paralegal | Paralegal | Clients | Cupcake LLC | ||
47 | $34,875 | Senior Partner | Partner | Clients | Cupcake LLC | ||
48 | $47,250 | Senior Partner | Partner | Clients | Cupcake LLC | ||
49 | $51,750 | Senior Partner | Partner | Clients | Cupcake LLC | ||
50 | $19,125 | Midlevel Associate | Associate | Clients | Éclair Inc | ||
51 | $28,125 | Senior Associate | Partner | Clients A | Éclair Inc | ||
52 | $22,500 | Senior Associate | Partner | Clients A | Éclair Inc | ||
53 | $27,000 | Senior Associate | Partner | Clients A | Éclair Inc | ||
54 | $13,275 | Paralegal | Paralegal | Clients | Éclair Inc | ||
55 | $13,275 | Paralegal | Paralegal | Clients | Éclair Inc | ||
56 | $13,950 | Paralegal | Paralegal | Clients | Éclair Inc | ||
57 | $31,500 | Midlevel Partner | Partner | Clients | Éclair Inc | ||
58 | $43,875 | Senior Partner | Partner | Clients | Éclair Inc | ||
59 | $38,250 | Senior Partner | Partner | Clients | Éclair Inc | ||
60 | $38,250 | Senior Partner | Partner | Clients | Éclair Inc | ||
61 | $38,250 | Senior Partner | Partner | Clients | Éclair Inc | ||
62 | $38,250 | Senior Partner | Partner | Clients | Éclair Inc | ||
63 | $16,425 | Junior Associate | Associate | Clients | Grant & Eisenhoffer P.A. | ||
64 | $16,425 | Junior Associate | Associate | Clients | Grant & Eisenhoffer P.A. | ||
65 | $33,750 | Senior Associate | Partner | Clients A | Grant & Eisenhoffer P.A. | ||
66 | $20,250 | Senior Associate | Partner | Clients A | Grant & Eisenhoffer P.A. | ||
67 | $9,450 | Paralegal | Paralegal | Clients | Grant & Eisenhoffer P.A. | ||
68 | $9,900 | Paralegal | Paralegal | Clients | Grant & Eisenhoffer P.A. | ||
69 | $45,000 | Senior Partner | Partner | Clients | Grant & Eisenhoffer P.A. | ||
70 | $45,000 | Senior Partner | Partner | Clients | Grant & Eisenhoffer P.A. | ||
71 | $24,750 | Senior Associate | Associate | Clients | Globex Corporation | ||
72 | $13,725 | Paralegal | Paralegal | Clients | Globex Corporation | ||
73 | $12,375 | Paralegal | Paralegal | Clients | Globex Corporation | ||
74 | $11,475 | Paralegal | Partner | Clients A | Globex Corporation | ||
75 | $10,125 | Paralegal | Partner | Clients A | Globex Corporation | ||
76 | $12,375 | Paralegal | Partner | Clients A | Globex Corporation | ||
77 | $11,700 | Paralegal | Partner | Clients A | Globex Corporation | ||
78 | $10,800 | Paralegal | Partner | Clients A | Home Furnishing | ||
79 | $10,125 | Paralegal | Paralegal | Clients | Home Furnishing | ||
80 | $13,725 | Paralegal | Paralegal | Clients | Home Furnishing | ||
81 | $10,125 | Paralegal | Paralegal | Clients | Home Furnishing | ||
82 | $35,100 | Midlevel Partner | Partner | Clients | Home Furnishing | ||
83 | $35,100 | Midlevel Partner | Partner | Clients | Home Furnishing | ||
84 | $22,950 | Midlevel Partner | Partner | Clients | Home Furnishing | ||
85 | $38,250 | Senior Partner | Partner | Clients | Home Furnishing | ||
86 | $35,100 | Senior Partner | Partner | Clients | Home Furnishing | ||
87 | $38,250 | Senior Partner | Partner | Clients | Home Furnishing | ||
88 | $36,900 | Senior Partner | Partner | Clients | Home Furnishing | ||
89 | $41,400 | Senior Partner | Partner | Clients | Home Furnishing | ||
90 | $38,250 | Senior Partner | Partner | Clients | Home Furnishing | ||
91 | $41,400 | Senior Partner | Partner | Clients | Home Furnishing | ||
92 | $38,250 | Senior Partner | Partner | Clients | Home Furnishing | ||
93 | $45,000 | Senior Partner | Partner | Clients | Home Furnishing | ||
94 | $38,250 | Senior Partner | Partner | Clients | Home Furnishing | ||
95 | $41,400 | Senior Partner | Partner | Clients | Home Furnishing | ||
96 | $47,250 | Senior Partner | Partner | Clients | Home Furnishing | ||
97 | $56,250 | Senior Partner | Partner | Clients | Home Furnishing | ||
98 | $43,875 | Senior Partner | Partner | Clients B | Home Furnishing | ||
99 | $43,875 | Senior Partner | Partner | Clients B | Home Furnishing | ||
100 | $56,250 | Senior Partner | Partner | Clients B | Home Furnishing | ||
101 | $58,500 | Senior Partner | Partner | Clients B | Home Furnishing | ||
102 | $39,375 | Senior Partner | Partner | Clients | Home Furnishing | ||
103 | $41,625 | Senior Partner | Partner | Clients | Home Furnishing | ||
104 | $43,875 | Senior Partner | Partner | Clients | Home Furnishing | ||
105 | $18,225 | Staff Attorney | Staff Attorney | Clients | Home Furnishing | ||
106 | $17,775 | Staff Attorney | Staff Attorney | Clients | Home Furnishing | ||
107 | $19,125 | Staff Attorney | Staff Attorney | Clients | Home Furnishing | ||
108 | $19,125 | Staff Attorney | Staff Attorney | Clients | Home Furnishing | ||
109 | $15,750 | Staff Attorney | Staff Attorney | Clients | Home Furnishing | ||
110 | $20,250 | Staff Attorney | Staff Attorney | Clients | Home Furnishing | ||
111 | $15,750 | Staff Attorney | Staff Attorney | Clients | Home Furnishing | ||
112 | $15,750 | Staff Attorney | Staff Attorney | Clients B | Home Furnishing | ||
113 | $16,875 | Staff Attorney | Staff Attorney | Clients B | Home Furnishing | ||
114 | $18,225 | Staff Attorney | Staff Attorney | Clients B | Home Furnishing | ||
115 | $18,225 | Staff Attorney | Staff Attorney | Clients | Home Furnishing | ||
116 | $18,000 | Staff Attorney | Staff Attorney | Clients | Home Furnishing | ||
117 | $19,125 | Staff Attorney | Staff Attorney | Clients | Home Furnishing | ||
118 | $19,125 | Staff Attorney | Staff Attorney | Clients | Home Furnishing | ||
119 | $14,175 | Staff Attorney | Staff Attorney | Clients | Home Furnishing | ||
120 | $15,750 | Staff Attorney | Staff Attorney | Clients | Home Furnishing | ||
121 | $19,125 | Staff Attorney | Staff Attorney | Clients | Home Furnishing | ||
122 | $20,250 | Staff Attorney | Staff Attorney | Clients | Home Furnishing | ||
123 | $20,250 | Junior Associate | Associate | Clients | Hooli | ||
124 | $20,250 | Junior Associate | Associate | Clients | Hooli | ||
125 | $20,250 | Junior Associate | Associate | Clients | Hooli | ||
126 | $20,250 | Junior Associate | Partner | Clients A | Hooli | ||
127 | $16,875 | Junior Associate | Partner | Clients A | Hooli | ||
128 | $20,250 | Junior Associate | Partner | Clients A | Hooli | ||
129 | $20,250 | Junior Associate | Partner | Clients A | Hooli | ||
130 | $20,250 | Junior Associate | Partner | Clients A | Hooli | ||
131 | $18,000 | Midlevel Associate | Partner | Clients A | Hooli | ||
132 | $20,250 | Midlevel Associate | Partner | Clients A | Hooli | ||
133 | $18,675 | Midlevel Associate | Partner | Clients A | Hooli | ||
134 | $23,175 | Midlevel Associate | Associate | Clients | Hooli | ||
135 | $19,575 | Midlevel Associate | Associate | Clients | Hooli | ||
136 | $18,675 | Midlevel Associate | Associate | Clients | Hooli | ||
137 | $33,750 | Senior Associate | Associate | Clients | Hooli | ||
138 | $20,250 | Senior Associate | Associate | Clients | Hooli | ||
139 | $15,750 | Senior Associate | Associate | Clients | Hooli | ||
140 | $28,350 | Senior Associate | Associate | Clients | Hooli | ||
141 | $23,175 | Senior Associate | Associate | Clients | Hooli | ||
142 | $21,375 | Senior Associate | Associate | Clients | Hooli | ||
143 | $20,250 | Senior Associate | Associate | Clients | Hooli | ||
144 | $20,700 | Senior Associate | Associate | Clients | Hooli | ||
145 | $20,250 | Senior Associate | Associate | Clients | Hooli | ||
146 | $19,125 | Senior Associate | Associate | Clients | Hooli | ||
147 | $21,600 | Senior Associate | Associate | Clients | Hooli | ||
148 | $21,825 | Senior Associate | Associate | Clients | Hooli | ||
149 | $20,250 | Senior Associate | Associate | Clients | Hooli | ||
150 | $14,400 | Paralegal | Paralegal | Clients | Hooli | ||
151 | $35,100 | Junior Partner | Partner | Clients | Hooli | ||
152 | $41,400 | Senior Partner | Partner | Clients B | Hooli | ||
153 | $45,000 | Senior Partner | Partner | Clients B | Hooli | ||
154 | $45,000 | Senior Partner | Partner | Clients B | Hooli | ||
155 | $32,625 | Senior Partner | Partner | Clients | Hooli | ||
156 | $46,125 | Senior Partner | Partner | Clients | Hooli | ||
157 | $41,400 | Senior Partner | Partner | Clients | Hooli | ||
158 | $36,675 | Senior Partner | Partner | Clients | Hooli | ||
159 | $20,700 | Staff Attorney | Staff Attorney | Clients | Hooli | ||
160 | $25,875 | Junior Associate | Associate | Clients | Initech | ||
161 | $18,000 | Junior Associate | Associate | Clients | Initech | ||
162 | $16,875 | Junior Associate | Associate | Clients | Initech | ||
163 | $37,800 | Of Counsel | Of Counsel | Clients | Initech | ||
164 | $24,300 | Of Counsel | Partner | Clients A | Initech | ||
165 | $49,050 | Of Counsel | Partner | Clients A | Initech | ||
166 | $12,375 | Paralegal | Partner | Clients A | Initech | ||
167 | $13,050 | Paralegal | Partner | Clients A | Initech | ||
168 | $12,375 | Paralegal | Partner | Clients A | Initech | ||
169 | $34,650 | Junior Partner | Partner | Clients A | Initech | ||
170 | $34,200 | Junior Partner | Partner | Clients A | Initech | ||
171 | $30,375 | Junior Partner | Partner | Clients A | Initech | ||
172 | $34,200 | Junior Partner | Partner | Clients | Initech | ||
173 | $35,100 | Midlevel Partner | Partner | Clients | Initech | ||
174 | $37,800 | Senior Partner | Partner | Clients | Initech | ||
175 | $36,900 | Senior Partner | Partner | Clients | Initech | ||
176 | $43,650 | Senior Partner | Partner | Clients B | Initech | ||
177 | $54,000 | Senior Partner | Partner | Clients | Initech | ||
178 | $43,650 | Senior Partner | Partner | Clients | Initech | ||
179 | $49,500 | Senior Partner | Partner | Clients | Initech | ||
180 | $46,125 | Senior Partner | Partner | Clients | Umbrella Corporation | ||
181 | $48,600 | Senior Partner | Partner | Clients A | Umbrella Corporation | ||
182 | $44,775 | Senior Partner | Partner | Clients A | Umbrella Corporation | ||
183 | $51,750 | Senior Partner | Partner | Clients A | Umbrella Corporation | ||
184 | $42,750 | Senior Partner | Partner | Clients A | Umbrella Corporation | ||
185 | $37,800 | Senior Partner | Partner | Clients A | Umbrella Corporation | ||
186 | $39,150 | Senior Partner | Partner | Clients A | Umbrella Corporation | ||
187 | $51,750 | Senior Partner | Partner | Clients | Umbrella Corporation | ||
188 | $49,500 | Senior Partner | Partner | Clients | Umbrella Corporation | ||
189 | $40,275 | Senior Partner | Partner | Clients | Umbrella Corporation | ||
190 | $60,750 | Senior Partner | Partner | Clients B | Umbrella Corporation | ||
191 | $41,400 | Senior Partner | Partner | Clients B | Umbrella Corporation | ||
192 | $59,625 | Senior Partner | Partner | Clients B | Umbrella Corporation | ||
193 | $39,600 | Senior Partner | Partner | Clients B | Umbrella Corporation | ||
194 | $37,800 | Senior Partner | Partner | Clients | Umbrella Corporation | ||
195 | $37,350 | Senior Partner | Partner | Clients | Umbrella Corporation | ||
196 | $44,775 | Senior Partner | Partner | Clients | Umbrella Corporation | ||
197 | $19,125 | Staff Attorney | Staff Attorney | Clients | Umbrella Corporation | ||
198 | $19,125 | Staff Attorney | Staff Attorney | Clients | Umbrella Corporation | ||
199 | $19,125 | Staff Attorney | Staff Attorney | Clients | Umbrella Corporation | ||
200 | $19,125 | Staff Attorney | Staff Attorney | Clients | Umbrella Corporation | ||
201 | $20,025 | Staff Attorney | Staff Attorney | Clients | Umbrella Corporation | ||
202 | $19,125 | Staff Attorney | Staff Attorney | Clients | Umbrella Corporation | ||
Plaintiff All RawData-Combined |