Distinguish "text" in =REPT(text, COUNTIFS(...))

[bruh]

Hi guys. I have this table that I'm using to plot 3 different three criteria (size, weight, and eaten or not) for the tables on the right.

What I want to do is take the "help" cells and specify which fruit met that criteria. For instance, in cell J3, I want to change "help" to the fruit symbol "W" from B3. Especially, for cases like H5 where there are 2 different fruits that meet the small & light criteria (Kiwi and Grape) and make cell H5 read "K, G" instead of Help help".

Basically, in cell I4, the Orange met all 3 criteria (medium, average, eaten = no). Therefore, I want cell I4 to read "O". That's basically it. For all cells in those two tables on the right.

What would be more convenient if I could reference column B for all the fruit symbols. I want the same to happen for all the other fruits.
I'm not even sure =REPT() is the right way to go about this... Thank you in advance for the help.

 

[cmowla]

Then you can use this formula (instead of COUNTIF) to count the "visible"/surviving fruit symbols in column after the filters have been applied . . . get the frequency distribution. (After all, there are a limited number of fruit symbols.)

For example, applying the filter for size small, weight light, and eaten no: (I got this formula from this Legend!)

(So, no need for your original supporting tables. Just have the data set and this frequency distribution table. But you will probably want to put this frequency distribution table either on another sheet or somewhere that its last row is above where the data table begins. This way, when the raw data table is filter, the rows in the frequency distribution table don't get "lost".)

b.xlsb
	A	B	C	D	E	F	G	H
1		Symbol	Size	Weight	Eaten?		Symbol	Frequency
5	Kiwi	K	small	light	no		A	0
6	Grape	G	small	light	no		G	1
8							K	1
9							O	0
10							P	0
11							W	0
Sheet7

Cell Formulas
Range	Formula
H5:H6,H8:H11	H5	=SUMPRODUCT(SUBTOTAL(3,OFFSET(Table2[Symbol],ROW(Table2[Symbol])-MIN(ROW(Table2[Symbol])),,1))*(Table2[Symbol]=G5))

 

[RoryA]

If you don't mind the light/average/heavy headers being at the top, you could do what you want with a pivot table from the data model in 2016.

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Distinguish "text" in =REPT(text, COUNTIFS(...))
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.