Sumif with condition similar

[alfordtp]

When you filter a spreadsheet, you can find results that are similar to a condition. I have families of products that share a common 3 digits, but the rest of the model number is different. The 3 similar digits could be the first 3 digits, or it could be 3 digits starting with the 2nd digit. This spreadsheet has each complete model number on its own row. In that row is a number that represents a volume. I want to be able to sum the volume column if the model number contains the 3 digits.

Model Number	Volume
A hat	5
B hat	1
A glove	4
hat	8

<tbody>
</tbody>

In the above example, the total for "hat" = 5 + 1 + 8 = 14

I would type in a condition (family name) into another cell (D1) and the formula in D2.

 

[Marcelo Branco]

Try this

D1
hat

D2
=SUMPRODUCT(--ISNUMBER(SEARCH(D1,$A$2:$A$5)),$B$2:$B$5)

M.

 

[alfordtp]

I can always count on this forum! Love it! Thanks Marcelo!

 

[alfordtp]

Excel does funny things when there are numbers and letters in the same cell.

For the example above, if there was another column with a sku associated, like 123a = a hat, 123b = b hat, 124a = a glove, and 123 = hat. How could I apply the above formula, or something similar, if I wanted to find all "123" records and sum that volume column?

 

[Marcelo Branco]

Excel does funny things when there are numbers and letters in the same cell.

For the example above, if there was another column with a sku associated, like 123a = a hat, 123b = b hat, 124a = a glove, and 123 = hat. How could I apply the above formula, or something similar, if I wanted to find all "123" records and sum that volume column?

Could you provide an example and expected result?

M.

 

[alfordtp]

SKU	Model	Volume
123a	A hat	5
123b	B hat	1
124a	A glove	4
123	hat	8

<tbody>
</tbody>

SKU	Volume Sum
123	5 + 1 + 8 = 14

<tbody>
</tbody>

When I used the formula from above, it did not give me 14. I think it has to do with how the SKU column is stored as text or as a number. Is there a way around that?

 

[Marcelo Branco]

It worked perfectly for me

	A​	B​	C​
1​	SKU​	Model​	Volume​
2​	123a​	A hat​	5​
3​	123b​	B hat​	1​
4​	124a​	A glove​	4​
5​	123​	hat​	8​
6​
7​	SKU​	Volume Sum​
8​	123​	14​

<TBODY>
</TBODY>

Formula in B8
=SUMPRODUCT(--ISNUMBER(SEARCH(A8,$A$2:$A$5)),$C$2:$C$5)

M.

 

[alfordtp]

Yes, it does for me too. I checked again. I must of had a typo. Thanks again for your help.

 

[Marcelo Branco]

You are welcome and thanks for the feedback.

M.