I am using Office 2003 on Windows XP.

I am familiar with the use of "SUMIF" and formula arrays like
"{=Sum(IF(...)}", etc. However, I cannot come up with a formula that will do
the following:

I have two sheets, call them Sheet1 and Sheet2.
Sheet1 contains all the data. Sheet2 contains the formulas.

So in Sheet2, I need a formula that might look like the following:

{=SUM(IF(Sheet1!A2:A500="FMPA", IF(SEARCH(Sheet1!G2:G500, "*SWAP*",
1),Sheet1!M2:M500)))}

Of course the above doesn't work because you cannot use "Search" in an array
formula (or can you?).

The problem is, I need a sum function based on column "A" and a Search type
function on column "G". I suppose I could break this out into multiple
columns, but this is for another user and I would like to accomplish this in
one neat function if possible.

Could someone please post an example function that would do it in ONE
function?

Thanks much in advance.