numeric wildcard in sumifs

[sriche01]

Hi,

I have a list of item numbers. Each item number consists of a text string ending with 2 numeric digits. The text string denotes the product, the numbers denote the size. So here are some examples:

[TABLE="width: 116"]
<colgroup><col></colgroup><tbody>[TR]
[TD][TABLE="width: 116"]
<colgroup><col></colgroup><tbody>[TR]
[TD]POTE GOS32[/TD]
[/TR]
[TR]
[TD]POTE MCW10[/TD]
[/TR]
[TR]
[TD]POTE MCW32[/TD]
[/TR]
[TR]
[TD]POTE PIB10[/TD]
[/TR]
[TR]
[TD]POTE PIB32


[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Not all items have a similar size distribution.

Here's my thing: I have a column that has referenced my entire list to trim the size off the end of the item numbers - it no longer has numbers. Also, it has reduced the list to unique values. Thus in that column the above list appears this way:

[TABLE="width: 116"]
<colgroup><col></colgroup><tbody>[TR]
[TD][TABLE="width: 116"]
<colgroup><col></colgroup><tbody>[TR]
[TD]POTE GOS[/TD]
[/TR]
[TR]
[TD]POTE MCW[/TD]
[/TR]
[TR]
[TD]POTE PIB[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Now I want to refer back to both lists with a sumifs function.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Item#[/TD]
[TD]Qty[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]POTE GOS32[/TD]
[TD]64[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]POTE MCW10[/TD]
[TD]30[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]POTE MCW32[/TD]
[TD]128[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]POTE PIB10[/TD]
[TD]50[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]POTE PIB32[/TD]
[TD]256[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]POTE GOS[/TD]
[TD]Function in Question[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]POTE MCW[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]POTE PIB[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

So where I have "Function in Question" I want to sumif the above list with criteria being the cell to the left. Essentially summing all item number qty that has the same text prefix. My sumif will not work with a criteria of A8&"*" and I have read that * as a wildcard does not work for numbers. Is there any wildcard for numbers? or some obvious workaround I am missing?

Thanks in advance!

 

[sriche01]

Oh and just so you know, not all text string prefixes are the same length.

 

[gaz_chops]

It should work!

=SUMIF($A$18:$A$22,$A26&"*",$B$18:$B$22)

[TABLE="width: 130"]
<tbody>[TR]
[TD="width: 65"]POTE GOS32[/TD]
[TD="width: 65, align: right"]1[/TD]
[/TR]
[TR]
[TD]POTE MCW10[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]POTE MCW32[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]POTE PIB10[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]POTE PIB32[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]POTE GOS[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]POTE MCW[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]POTE PIB[/TD]
[TD="align: right"]9[/TD]
[/TR]
</tbody>[/TABLE]

 

[sriche01]

You are right... I feel foolish. The item I thought was a test because there should be a quantity present was showing zero. But it was supposed to because there was no quantity to sum. Thanks!

 

[gaz_chops]

You're welcome.