Complex sum function help - possible?

[Guest]

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.

 

[Bob Phillips]

How about

=SUMPRODUCT(--(Sheet1!A2:A500="FMPA"),--(ISNUMBER(FIND("SWAP",Sheet1!G2:G500
))),Sheet1!M2:M500)

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[Guest]

Thanks Bob, but I need a wildcard on the "SWAP" portion, like "*SWAP*".

When I change to that, I get #Value!
Is that possible?

 

[Guest]

Okay, I got it, it was partially a typo on my part, and I switched "Search"
for "Find" and it works great, thanks!

 

[Tom Ogilvy]

Search should work fine. Search and Find are equivalent except search is
case insensitive.


From the Excel help on Search:
a.. SEARCH and SEARCHB are not case sensitive. If you want to do a case
sensitive search, you can use FIND and FINDB.

 

[Bob Phillips]

The Find is a sub-string function, and Find will work on a case matching
basis.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)