Custom Aggregate using Words

[agerrard]

Does anyone know how to build a dynamic custom aggregate to look up key words in item description. For example i want to build totals for Ginger Beer and Low Carb Beer that are found in each items description. So i will have 000s of rows (that contain products) and in column B there will be sales for each item (or product). So from that table i only want to sum or build totals for products that contain the text "Ginger Beer" and "Low Carb Beer". Does anyone have an idea ? Thanks!

 

[myall_blues]

You can use wildcards for the text. Here's a very simple example that hopefully matches what you were looking for.

Book1
	A	B	C	D	E
1	Product type	Sales		Type	Total
2	Long way of saying Ginger Beer	10		Ginger beer	60
3	Another Ginger beer type	20		Low Carb Beer	18
4	Ginger beer	30
5	Low Carb Beer	5
6	Very Low Carb Beer	6
7	Even lower Low Carb Beer	7
Sheet1

Cell Formulas
Range	Formula
E2:E3	E2	=SUMIFS($B$2:$B$7,$A$2:$A$7,"*" & D2 & "*")

P.S. It's always helpful if you tell us what version of Excel you are using. You can update your profile to show that information.