Sum if with array (sumprod?)

hello,

I'm simply trying to add values next to cells containing a string of character. I'd like it to be usable in the future as in whenever you add a category or a new value within the categories it still works. So instead of A B C I could have hundreds of entries as to not want to do them one by one.

Cat1 is yellow and should give 51 but I have 32 (which is the sum of As only) and same for cat2 with FFs being 5 instead of 11.

The bottom part is where the results would be. The red background is what I tried so far to no avail while the green and yellow are the desired result.
Also, if I'd add D under Cat1 I would like Cat1 to include B9 as well (and any other D that gets added later) so new result would be 61.

Can array be used like that at all? They aren't of the same size at all which might be why sumprod doesn't work?

thanks

You have the 'array of arrays' issue - very hard or impossible to use single cell formulas, but you can use columns of helper formulas, or you can use a UDF, as in the attached.

Perhaps I've misunderstood the question, as I can't see Bernie's "array of arrays issue".

=SUMPRODUCT(SUMIF(A1:A9,"*"&Categ1&"*",B1:B9),0+(Categ1<>""))

Regards

You're welcome!

Cheers