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.[TABLE="class: grid, width: 20"]
<tbody>[TR]
[TD]Model Number[/TD]
[TD]Volume[/TD]
[/TR]
[TR]
[TD]A hat[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]B hat[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]A glove[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]hat[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]SKU[/TD]
[TD]Model[/TD]
[TD]Volume[/TD]
[/TR]
[TR]
[TD]123a[/TD]
[TD]A hat[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]123b[/TD]
[TD]B hat[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]124a[/TD]
[TD]A glove[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]hat[/TD]
[TD]8[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]SKU[/TD]
[TD]Volume Sum[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]5 + 1 + 8 = 14[/TD]
[/TR]
</tbody>[/TABLE]

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


[TABLE="class: grid"]
<TBODY>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]

SKU​

[/TD]
[TD]

Model​

[/TD]
[TD]

Volume​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

123a​

[/TD]
[TD]

A hat​

[/TD]
[TD]

5​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

123b​

[/TD]
[TD]

B hat​

[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

124a​

[/TD]
[TD]

A glove​

[/TD]
[TD]

4​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

123​

[/TD]
[TD]

hat​

[/TD]
[TD]

8​

[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]

SKU​

[/TD]
[TD]

Volume Sum​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]

123​

[/TD]
[TD]

14​

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


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.