Complex sum function help - possible?
 

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.

Complex sum function help - possible?
 

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)

"XP" wrote in message
...
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.

Complex sum function help - possible?
 

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

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

"Bob Phillips" wrote:

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)

"XP" wrote in message
...
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.

Complex sum function help - possible?
 

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

"XP" wrote:

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

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

"Bob Phillips" wrote:

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)

"XP" wrote in message
...
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.

Complex sum function help - possible?
 

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.
--
Regards,
Tom Ogilvy


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

"XP" wrote:

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

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

"Bob Phillips" wrote:

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)

"XP" wrote in message
...
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.

Complex sum function help - possible?
 

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)

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

"XP" wrote:

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

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

"Bob Phillips" wrote:

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)

"XP" wrote in message
...
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.