Countifs variable crietera, avoid hardcodes?

[Will85]

Sheet1 Column A has values throughout, the values are "Apples", "Oranges", "Cookies", "Apples"
Sheet 2 has a table, A1 = Apples, B1 = Healthy, A2 = Oranges, B2 = Healthy, A3 = Cookies, B3 = Unhealthy, A4 = Apples, B4 = Healthy
Sheet 3 A1 = Healthy, B1 = Unhealthy


A2 & B2 = I need a formula to count the number of times a Healthy or Unhealthy value appeared in Sheet1 Column A

I dont want hardcodes, I might change the value in Sheet2 Column B, so I need it to be dynamic. I think I want a countif array formula, but I am struggling.

 

[Will85]

Using the table definition will be easier to understand debug and most resource efficient. But, you have a solution that works so that is good.
As far as the month, it would just be one more parenthetical comparison statement in the formula. I'll post a formula in a moment. But again, this brings me back to my historical data integrity questions I made earlier. By changing your Legend you are messing up prior reports that use the same legend source. but if you want to see the updated health statuses looking back then this works. Hold on for an updated formula for months included.

I appreciate your concern, this is obviously just test data. My real world situation is not concerned about historical, but I do appreciate your attention to detail. Ill look out for your update.

 

[awoohaw]

OOF, that really added more complexity to it. I needed to use some functions that are only in Office 365.
But here it is.

mr excel questions 29.xlsm
	A	B	C	D	E	F	G	H	I
1	Data	Month		Food	Rating			Healthy	Unhealthy
2	Cookies	December		Cookies	Healthy		January	16	13
3	Apples	November		Apples	Unhealthy		February	17	14
4	Cookies	May		Oranges	Healthy		March	11	10
5	Oranges	November		Pears	Unhealthy		April	13	12
6	Pears	December		Watermelon	Unhealthy		May	15	11
7	Apples	March		Candy	Healthy		June	11	8
8	Cookies	November		SRF	Unhealthy		July	10	9
9	Pears	June		PCK	Healthy		August	14	13
10	Watermelon	May		CQW	Healthy		September	14	14
11	Candy	August		HDO	Healthy		October	14	15
12	Candy	August		ONY	Healthy		November	11	8
13	Apples	April		JXR	Unhealthy		December	11	16
14	SRF	May		SIJ	Unhealthy
15	PCK	September		JGG	Healthy
16	CQW	February		JMH	Unhealthy
17	HDO	March		RXV	Unhealthy
18	ONY	August		FDP	Healthy
19	JXR	June		XSQ	Unhealthy
20	SIJ	July		OWI	Healthy
21	JGG	April		TLG	Unhealthy
22	JMH	July		NLE	Unhealthy
23	RXV	April		Pickles	Healthy
24	FDP	January		Tomatoes	Healthy
25	XSQ	January		Jelly Beans	Healthy
26	OWI	July
27	TLG	August
28	NLE	April
29	Pickles	June
30	NLE	October
31	Jelly Beans	January
32	Cookies	November
33	Apples	August
34	Cookies	August
35	Oranges	March
36	Pears	January
37	Apples	November
38	Cookies	January
39	Pears	August
40	Watermelon	February
41	Candy	July
42	Candy	July
43	Apples	December
44	SRF	October
45	PCK	September
46	CQW	February
47	HDO	May
48	ONY	February
49	JXR	March
50	SIJ	December
51	JGG	December
52	JMH	January
53	RXV	October
54	FDP	December
55	XSQ	January
56	OWI	August
57	TLG	April
58	NLE	May
59	Pickles	June
60	Tomatoes	July
61	Cookies	December
62	Apples	May
63	Cookies	November
64	Oranges	March
65	Pears	March
66	Apples	December
67	Cookies	August
68	Pears	June
69	Watermelon	October
70	Candy	February
71	Candy	February
72	Apples	February
73	SRF	April
74	PCK	October
75	CQW	October
76	HDO	September
77	ONY	October
78	JXR	September
79	SIJ	December
80	JGG	March
81	JMH	May
82	RXV	September
83	FDP	December
84	XSQ	September
85	OWI	November
86	TLG	October
87	NLE	July
88	Pickles	February
89	Tomatoes	September
90	Cookies	August
91	Apples	October
92	Cookies	March
93	Oranges	February
94	Pears	November
95	Apples	February
96	Cookies	May
97	Pears	December
98	Watermelon	February
99	Candy	February
100	Candy	January
101	Apples	September
102	SRF	August
103	PCK	August
104	CQW	June
105	HDO	June
106	ONY	September
107	JXR	August
108	SIJ	June
109	JGG	October
110	JMH	September
111	RXV	March
112	FDP	May
113	XSQ	July
114	OWI	May
115	TLG	December
116	NLE	February
117	Pickles	December
118	Tomatoes	February
119	Jelly Beans	September
120	Cookies	January
121	Apples	August
122	Cookies	February
123	Oranges	August
124	Pears	January
125	Apples	January
126	Cookies	November
127	Pears	May
128	Watermelon	August
129	Candy	January
130	Candy	April
131	Apples	July
132	SRF	December
133	PCK	August
134	CQW	March
135	HDO	February
136	ONY	May
137	JXR	February
138	SIJ	October
139	JGG	July
140	JMH	December
141	RXV	November
142	FDP	December
143	XSQ	August
144	OWI	January
145	TLG	May
146	NLE	September
147	Pickles	June
148	Tomatoes	October
149	Jelly Beans	May
150	Cookies	May
151	Apples	July
152	Cookies	October
153	Oranges	March
154	Pears	May
155	Apples	January
156	Cookies	September
157	Pears	October
158	Watermelon	April
159	Candy	December
160	Candy	October
161	Apples	August
162	SRF	June
163	PCK	July
164	CQW	August
165	HDO	May
166	ONY	December
167	JXR	December
168	SIJ	October
169	JGG	January
170	JMH	September
171	RXV	December
172	FDP	April
173	XSQ	October
174	OWI	September
175	TLG	January
176	NLE	October
177	Pickles	January
178	Tomatoes	January
179	Jelly Beans	November
180	Cookies	June
181	Apples	April
182	Cookies	April
183	Oranges	November
184	Pears	January
185	Apples	September
186	Cookies	February
187	Pears	December
188	Watermelon	February
189	Candy	July
190	Candy	April
191	Apples	July
192	SRF	June
193	PCK	March
194	CQW	April
195	HDO	August
196	ONY	October
197	JXR	April
198	SIJ	December
199	JGG	April
200	JMH	March
201	RXV	October
202	FDP	January
203	XSQ	September
204	OWI	May
205	TLG	September
206	NLE	March
207	Pickles	May
208	Tomatoes	August
209	Jelly Beans	May
210	Cookies	October
211	Apples	May
212	Cookies	January
213	Oranges	October
214	Pears	January
215	Apples	May
216	Cookies	May
217	Pears	September
218	Watermelon	December
219	Candy	July
220	Candy	September
221	Apples	February
222	SRF	February
223	PCK	April
224	CQW	May
225	HDO	October
226	ONY	April
227	JXR	August
228	SIJ	November
229	JGG	June
230	JMH	November
231	RXV	December
232	FDP	September
233	XSQ	July
234	OWI	October
235	TLG	February
236	NLE	November
237	Pickles	November
238	Tomatoes	October
239	Jelly Beans	April
240	Cookies	June
241	Apples	October
242	Cookies	February
243	Oranges	February
244	Pears	February
245	Apples	March
246	Cookies	June
247	Pears	March
248	Watermelon	September
249	Candy	May
250	Candy	August
251	Apples	July
252	SRF	June
253	PCK	June
254	CQW	April
255	HDO	September
256	ONY	March
257	JXR	February
258	SIJ	April
259	JGG	February
260	JMH	November
261	RXV	September
262	FDP	March
263	XSQ	June
264	OWI	October
265	TLG	August
266	NLE	May
267	Pickles	January
268	Tomatoes	April
269	Jelly Beans	January
270	Cookies	February
271	Apples	September
272	Cookies	November
273	Oranges	November
274	Pears	January
275	Apples	April
276	Cookies	January
277	Pears	March
278	Watermelon	February
279	Candy	February
280	Candy	December
281	Apples	January
282	SRF	February
283	PCK	March
284	CQW	April
285	HDO	September
286	ONY	September
287	JXR	August
288	SIJ	December
289	JGG	July
290	JMH	August
291	RXV	January
292	FDP	December
293	XSQ	April
294	OWI	July
295	TLG	April
296	NLE	October
297	Pickles	September
298	Tomatoes	June
299	Jelly Beans	January
300	TLG	March
301	NLE	October
WillB85

Cell Formulas
Range	Formula
H2:I2,I3:I13	H2	=LET(a,FILTER(Data,Data[Month]=$G2,""),b,TAKE(a,,1),c,INDEX(Legend[Rating],MATCH(b,Legend[Food],0)), d,SUM(--(c=H$1)),d)
H3:H13	H3	=LET(a,FILTER(Data,Data[Month]=G3,""),b,TAKE(a,,1),c,INDEX(Legend[Rating],MATCH(b,Legend[Food],0)), d,SUM(--(c=H$1)),d)