Can't spill with SUM

[BrianGGG]

Hello. I'm failing at using Dynamic Arrays to completely solve my problem.
A brief description: I have a table of items, and I would like to list each letter of the alphabet along with a count of the number of items that start with that letter.

Here is a sample table that I have called "t_foods":

	B
Food
Apple
Orange
Artichoke
Banana
Cheese
*******s
Cream
Eggs

<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]9[/TD]

[TD="align: center"]10[/TD]

</tbody>

Sheet1​

I have created a list of letters in the alphabet with the following formula:
[TABLE="width: 354"]
<tbody>[TR]
[TD="class: xl63, width: 354"]=CHAR(SEQUENCE(26,1,65))[/TD]
[/TR]
</tbody>[/TABLE]

and I was able to solve my problem using "a traditional formula" with the following:

[TABLE="width: 354"]
<tbody>[TR]
[TD="class: xl65, width: 354"]=SUM(--(LEFT(t_foods[Food])=E2))


Here's an excerpt of what the answer looks like with these two formulas:

	E	F
Letters	Traditional
A
B
C
D
E
F

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]3[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]0[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]=CHAR(SEQUENCE(26,1,65))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]=SUM(--(LEFT(t_foods[Food])=E2))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F3[/TH]
[TD="align: left"]=SUM(--(LEFT(t_foods[Food])=E3))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F4[/TH]
[TD="align: left"]=SUM(--(LEFT(t_foods[Food])=E4))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F5[/TH]
[TD="align: left"]=SUM(--(LEFT(t_foods[Food])=E5))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F6[/TH]
[TD="align: left"]=SUM(--(LEFT(t_foods[Food])=E6))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F7[/TH]
[TD="align: left"]=SUM(--(LEFT(t_foods[Food])=E7))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]





My problem is...I can't figure out how to use a spilled formula to find the answer. My best attempt is the following formula:

SUM(--(LEFT(t_foods[Food])=E2#))


This only returns an "#N/A" error for me, and I can't seem to get the SUM function to spill under any circumstance. I even desperately tried to use CTRL-SHIFT-ENTER but no luck.


Anyone have any ideas how to use a dynamic array to solve my problem?




Thanks,
BrianGGG



[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

Why do you need a dynamic array formula, you can do what you need like

Excel 2013/2016
	B	C	D	E	F
2	Apple			A	2
3	Orange			B	1
4	Artichoke			C	3
5	Banana			D	0
6	Cheese			E	1
7	*******s			F	0
8	Cream			G	0
9	Eggs			H	0
10				I	0
11				J	0
List

Cell Formulas
Range	Formula
E2	=CHAR(ROW(E2)+63)
F2	=COUNTIF(T_food[Food],E2&"*")

 

[BrianGGG]

Thanks for responding.

I went down the Dynamic road because my next step is to figure out how to only show the letters that have at least one value. In your example above, I'd only want to print out A, B, C, E because they are the only letters that have any entries.
My next step was to use FILTER to limit the letters.

I suppose I could use the structure above as a "helper table" to come up with an answer, but are there any ideas about how to do this dynamically?

 

[Eric W]

I don't have the new array functions yet, so I can't test this, but this seems plausible:

=COUNTIF(t_foods[Food],CHAR(SEQUENCE(26, 1, 65))&"*")

or maybe

=COUNTIF(t_foods[Food],E2#&"*")

See if that works for you.

 

[BrianGGG]

Thanks Eric W.

Both of your formulas worked. I chose the second one because it allows me to filter to only the letters that have at least one value as follows:

	E	G
Letters	Dynamic
A
B
C
E
O

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]3[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]1[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]=FILTER(CHAR(SEQUENCE(26,1,65)),COUNTIF(t_foods[Food],CHAR(SEQUENCE(26, 1, 65))&"*"))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G2[/TH]
[TD="align: left"]=COUNTIF(t_foods[Food],E2#&"*")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

Nice! I'm looking forward to using those functions at some time. In the meantime, I wonder if this formula might work instead of your E2 formula:

=SORT(UNIQUE(LEFT(t_foods[Food])))

There might be some issues with upper/lower case, so you may need an UPPER around the LEFT function too.

 

[BrianGGG]

This formula is great also, thanks.

Half of me is appreciative, half of me wonders how you are better at dynamic arrays than I am when you don't even have them yet!


BrianGGG

 

[Eric W]

This formula is great also, thanks.

Half of me is appreciative, half of me wonders how you are better at dynamic arrays than I am when you don't even have them yet!


BrianGGG

My wife says I have "the knack":

https://www.youtube.com/watch?v=g8vHhgh6oM0&feature=youtu.be


But I'm glad it's helpful to you!