Sumif

[jlkirk]

In Column A, in each cell in rows 1-10, there is various text that may or may not include "ABC" and "DEFG" as the first four letters. In Column B, rows 1-10, there is numerical data. What I would like to do in Column C, rows 1-10, is place a formula that would return the contents of the respective cell in Column B, but only where the first three or four letters in the respective cell in column A is "ABC" or "DEFG". Any ideas?
Thanks in advance.

 

[jlkirk]

Thanks again. So it is not possible to short-cut the "OR" argument when it is embedded in a SUMPRODUCT function (?)

 

[RoryA]

I don't believe so in this case. If you were looking for exact matches, you could use an array constant, but you aren't.

 

[barry houdini]

If you have a longer list of criteria to check you could use a formula like

=SUMPRODUCT((D1:D10=111)*ISNUMBER(FIND("^^"&{"ABC","DEFG"},"^^"&A1:A10))*B1:B10)

where "^^" is some text that won't appear in your data

......or if you are using Excel 2007 you could use SUMIFS function and revert to wildcards

 

[jlkirk]

Thanks everyone.