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.

 

[kweaver]

Maybe something like this?

042523FunctionSyntax.xlsm
	A	B	C
1		Healthy	Unhealthy
2	Apples	2	0
3	Oranges	1	0
4	Cookies	0	1
5	Apples	2	0
Foods

Cell Formulas
Range	Formula
B1:C1	B1	=Criteria!A1
B2:C5	B2	=COUNTIFS(Status!$A$1:$A$4,$A2,Status!$B$1:$B$4,B$1)

Where "Status" is:

Apples	Healthy
Oranges	Healthy
Cookies	Unhealthy
Apples	Healthy

 

[awoohaw]

Just a question about this: "I might change the value in Sheet2 Column B, so I need it to be dynamic."
Do you want the prior lookups to change to the updated return values? If not, then you probably need a VBA solution, or you need to convert the existing lookup formulas to values before changing it (which is no a good idea either).

 

[Will85]

Maybe something like this?

042523FunctionSyntax.xlsm
	A	B	C
1		Healthy	Unhealthy
2	Apples	2	0
3	Oranges	1	0
4	Cookies	0	1
5	Apples	2	0
Foods

Cell Formulas
Range	Formula
B1:C1	B1	=Criteria!A1
B2:C5	B2	=COUNTIFS(Status!$A$1:$A$4,$A2,Status!$B$1:$B$4,B$1)

Where "Status" is:

Apples	Healthy
Oranges	Healthy
Cookies	Unhealthy
Apples	Healthy

That is not what I am looking for. I dont want a formula for each instance.

I need an array, or sumproduct I think imbeded in my countif,

 

[Will85]

Just a question about this: "I might change the value in Sheet2 Column B, so I need it to be dynamic."
Do you want the prior lookups to change to the updated return values? If not, then you probably need a VBA solution, or you need to convert the existing lookup formulas to values before changing it (which is no a good idea either).

I want the criteria of which food is healthy or unhealthy to be dynamic. So I want to be able to change Apples from Healthy to Unhealthy, and have my count update.

The data I get just has a list of the food.
Whether the food is healthy or unhealthy changes frequently, so I dont want to have to keep changing my countif formula to include one food or exclude another.

I have a table with all of the possible food that would appear, and I want to be able to update the healthy or unhealthy value and have my countif update. I could simply use a helper column, but I dont want to. I feel like this can be accomplished with an array formula somehow.

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H
1							Healthy	Unhealthy
2	Apples	Healthy				Apples	3	2
3	Oranges	Healthy				Oranges
4	Cookies	Unhealthy				Cookies
5	Apples	Healthy				cookies
6
Dashboard

Cell Formulas
Range	Formula
G2:H2	G2	=SUM(COUNTIFS($B:$B,G1,$A:$A,$F$2:$F$5))

 

[kweaver]

I want the criteria of which food is healthy or unhealthy to be dynamic. So I want to be able to change Apples from Healthy to Unhealthy, and have my count update.

The data I get just has a list of the food.
Whether the food is healthy or unhealthy changes frequently, so I dont want to have to keep changing my countif formula to include one food or exclude another.

I have a table with all of the possible food that would appear, and I want to be able to update the healthy or unhealthy value and have my countif update. I could simply use a helper column, but I dont want to. I feel like this can be accomplished with an array formula somehow.

In my example, you can change the dynamics and the results will change automatically.

 

[awoohaw]

I want the criteria of which food is healthy or unhealthy to be dynamic. So I want to be able to change Apples from Healthy to Unhealthy, and have my count update.

The data I get just has a list of the food.
Whether the food is healthy or unhealthy changes frequently, so I dont want to have to keep changing my countif formula to include one food or exclude another.

I have a table with all of the possible food that would appear, and I want to be able to update the healthy or unhealthy value and have my countif update. I could simply use a helper column, but I dont want to. I feel like this can be accomplished with an array formula somehow.

i am pretty sure I understood what you wrote there. I am asking do you want to keep data from prior lookups.
For instance on 2/15/2023 you may have testing Apples and they are then unhealhty
But in March you changed them to be healthy. Do you want that 2/15/2023 data to also be updated?

 

[Will85]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H
1							Healthy	Unhealthy
2	Apples	Healthy				Apples	3	2
3	Oranges	Healthy				Oranges
4	Cookies	Unhealthy				Cookies
5	Apples	Healthy				cookies
6
Dashboard

Cell Formulas
Range	Formula
G2:H2	G2	=SUM(COUNTIFS($B:$B,G1,$A:$A,$F$2:$F$5))

Data list is constantly growing
Legend is variable, blue highlight may change (apple may be considered healthy one week, then unhealthy the next)
Yellow is where I want a formula, to count the number of Healthy and Unhealthy items in the data column, based on the legend.

I am trying to accomplish this without a helper column.

 

[Fluff]

Did you try the formula I suggested?