Countifs variable crietera, avoid hardcodes?

Will85

Active Member
Joined
Apr 26, 2012
Messages
254
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Maybe something like this?

042523FunctionSyntax.xlsm
ABC
1HealthyUnhealthy
2Apples20
3Oranges10
4Cookies01
5Apples20
Foods
Cell Formulas
RangeFormula
B1:C1B1=Criteria!A1
B2:C5B2=COUNTIFS(Status!$A$1:$A$4,$A2,Status!$B$1:$B$4,B$1)


Where "Status" is:


ApplesHealthy
OrangesHealthy
CookiesUnhealthy
ApplesHealthy
 
Upvote 0
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).
 
Upvote 0
Maybe something like this?

042523FunctionSyntax.xlsm
ABC
1HealthyUnhealthy
2Apples20
3Oranges10
4Cookies01
5Apples20
Foods
Cell Formulas
RangeFormula
B1:C1B1=Criteria!A1
B2:C5B2=COUNTIFS(Status!$A$1:$A$4,$A2,Status!$B$1:$B$4,B$1)


Where "Status" is:


ApplesHealthy
OrangesHealthy
CookiesUnhealthy
ApplesHealthy
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,
 
Upvote 0
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.
 
Upvote 0
How about
Fluff.xlsm
ABCDEFGH
1HealthyUnhealthy
2ApplesHealthyApples32
3OrangesHealthyOranges
4CookiesUnhealthyCookies
5ApplesHealthycookies
6
Dashboard
Cell Formulas
RangeFormula
G2:H2G2=SUM(COUNTIFS($B:$B,G1,$A:$A,$F$2:$F$5))
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
How about
Fluff.xlsm
ABCDEFGH
1HealthyUnhealthy
2ApplesHealthyApples32
3OrangesHealthyOranges
4CookiesUnhealthyCookies
5ApplesHealthycookies
6
Dashboard
Cell Formulas
RangeFormula
G2:H2G2=SUM(COUNTIFS($B:$B,G1,$A:$A,$F$2:$F$5))

Countif Array Formula.PNG


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.
 
Upvote 0
Did you try the formula I suggested?
 
Upvote 0

Forum statistics

Threads
1,223,908
Messages
6,175,304
Members
452,633
Latest member
DougMo

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