sumif with wildcard

Posted by Andonny on September 15, 2001 8:43 PM

Hi,
I am trying to sum in column D the totals of column B where column C matches with a PEAR in column A. The problem I am having is that in column A it is not just PEAR. It is in this case 55 PEAR 22. Sometimes I have PEAR 22only. Usually I would just use =SUMIF(A:A10,C1,B1:B10) but it won't workin this case.
..............A............B.............C..........D
1.......55PEAR22........0.5.......PEAR.......1.00
2.......7XAPPLET2.......0.3.......APPLE......0.30
3.......55PEAR22........0.5

Thanks a lot for your help
Andonny

Posted by Aladin Akyurek on September 15, 2001 11:58 PM

Andonny,

Use one of:

[1] =SUMIF($A$1:$A$3,"*"&C1&"*",$B$1:$B$3)

[2] =SUMPRODUCT((ISNUMBER(SEARCH(C1,$A$1:$A$3)))*($B$1:$B$3))

Aladin

============