SUMIF a range with a ISNUMBER SEARCH criteria

[Bram1212]

Hi!

I am trying to sum up a column that has different product names. For instance a Batavia Grand Vista and a Gazelle Grand Vista both have a sales value and I am trying to sum up all the Grand Vista's.

I found a formula using SUMPRODUCT combined with ISNUMBER but I can't get it to apply to my ranges. The formula I found is:
=SUMPRODUCT(H2:H1000;--(ISNUMBER(SEARCH("Strategic";G2:G1000)));--(E2:E1000="Name"))

The columns and search criteria do not apply to my range at this moment. My data is stored in:

Column B: All the different product names.
Column C: The sales value I would like to sum up.
Column I: (Or I29 for one specific search criteria) the product family names which I want so sum up out of column B/C.

Hopefully you can help me out, thanks in advance!




[FONT=&quot]


[/FONT]

 

[FormR]

Hi, something like this maybe:

Excel 2013/2016
	B	C	D	E	F	G	H	I	J
28	name	sales value						search crit	sum
29	Batavia Grand Vista	89						grand vista	165
30	Gazelle Grand Vista	43
31	other1	81
32	other2	12
33	another Grand Vista	33
34	other1	48
35	hello	64
Sheet1

Cell Formulas
Range	Formula
J29	=SUMIFS(C:C,B:B,"*"&I29&"*")

 

[Bram1212]

Yes this is exactly what I was looking for! Thanks!

Can you explain how this one works? What does the "*" add to the formula?

 

[FormR]

What does the "*" add to the formula?

Hi, it's a wildcard character - if you search "using wildcards in excel sumifs" you should get quite a few hits with more detailed explanations.

 

[Bram1212]

Will do, thanks again for the help.