Multiple criterion including wildcards to sum in a range
 

Can someone help me please? In the example below, what formula would I use
to sum data in column C that relates to Fred's combined pensions. Note the
string 'pension' or 'pensions' is in not particular position.

Col A Col B Col C
multi sector - Pensions Fred $100,000
Pension - Equity Fred $150,000
Pensions - Lifetime Fred $100,000
Equities Fred $90,000
Property Fred $45,000
Pension - Allocated Mary $50,000
multi sector - Pensions Mary $75,000
Pension - Equity Mary $100,000

I think I need to nest criterion bit despite trying examples given in
discussion posts on this site am having trouble getting anything to work.

If you can help, thanks in advance.
John.

=SUMPRODUCT(--ISNUMBER(SEARCH(D2,$A$2:$A$100)),--($B$2:$B$100=E2),$C$2:$C$100)

where D2 houses the word Pension and E2 the name Fred.

Note that this type of formulas does not admit whole column references
like A:A.

JEH wrote:
Can someone help me please? In the example below, what formula would I use
to sum data in column C that relates to Fred's combined pensions. Note the
string 'pension' or 'pensions' is in not particular position.

Col A Col B Col C
multi sector - Pensions Fred $100,000
Pension - Equity Fred $150,000
Pensions - Lifetime Fred $100,000
Equities Fred $90,000
Property Fred $45,000
Pension - Allocated Mary $50,000
multi sector - Pensions Mary $75,000
Pension - Equity Mary $100,000

I think I need to nest criterion bit despite trying examples given in
discussion posts on this site am having trouble getting anything to work.

If you can help, thanks in advance.
John.

One way


=SUMPRODUCT(--(ISNUMBER(SEARCH("pension",A2:A9))),--(B2:B9="Fred"),C2:C9)



--
Regards,

Peo Sjoblom

(No private emails please)


"JEH" wrote in message
...
Can someone help me please? In the example below, what formula would I
use
to sum data in column C that relates to Fred's combined pensions. Note
the
string 'pension' or 'pensions' is in not particular position.

Col A Col B Col C
multi sector - Pensions Fred $100,000
Pension - Equity Fred $150,000
Pensions - Lifetime Fred $100,000
Equities Fred $90,000
Property Fred $45,000
Pension - Allocated Mary $50,000
multi sector - Pensions Mary $75,000
Pension - Equity Mary $100,000

I think I need to nest criterion bit despite trying examples given in
discussion posts on this site am having trouble getting anything to work.

If you can help, thanks in advance.
John.

"JEH" wrote...
Can someone help me please? In the example below, what formula would I use
to sum data in column C that relates to Fred's combined pensions. Note the
string 'pension' or 'pensions' is in not particular position.

Col A Col B Col C
multi sector - Pensions Fred $100,000
Pension - Equity Fred $150,000
Pensions - Lifetime Fred $100,000
Equities Fred $90,000
Property Fred $45,000
Pension - Allocated Mary $50,000
multi sector - Pensions Mary $75,000
Pension - Equity Mary $100,000
....

=SUMPRODUCT(ISNUMBER(SEARCH("PENSION",A2:A9))*(B2: B9="Fred"),C2:C9)

or the array formula

=SUM(IF(B2:B9="Fred",IF(ISNUMBER(SEARCH("PENSION", A2:A9)),C2:C9)))

Thank you very much. I would never have got there on my own! Can D2 house a
wildcard, such as "*pens*" ?

"Aladin Akyurek" wrote:

=SUMPRODUCT(--ISNUMBER(SEARCH(D2,$A$2:$A$100)),--($B$2:$B$100=E2),$C$2:$C$100)

where D2 houses the word Pension and E2 the name Fred.

Note that this type of formulas does not admit whole column references
like A:A.

JEH wrote:
Can someone help me please? In the example below, what formula would I use
to sum data in column C that relates to Fred's combined pensions. Note the
string 'pension' or 'pensions' is in not particular position.

Col A Col B Col C
multi sector - Pensions Fred $100,000
Pension - Equity Fred $150,000
Pensions - Lifetime Fred $100,000
Equities Fred $90,000
Property Fred $45,000
Pension - Allocated Mary $50,000
multi sector - Pensions Mary $75,000
Pension - Equity Mary $100,000

I think I need to nest criterion bit despite trying examples given in
discussion posts on this site am having trouble getting anything to work.

If you can help, thanks in advance.
John.

"JEH" wrote...
Thank you very much. I would never have got there on my own!
Can D2 house a wildcard, such as "*pens*" ?
....

It could, but *pens* could match more than just pension or pensions. It
could match pensive, happens, Happenstance Income, Expenses, etc. If you
want to match pension, don't get lazy.

I agree but just in this set of data it can only refer to pensions, but there
could be several truncations.

Thanks for your help.

"Harlan Grove" wrote:

"JEH" wrote...
Thank you very much. I would never have got there on my own!
Can D2 house a wildcard, such as "*pens*" ?
....

It could, but *pens* could match more than just pension or pensions. It
could match pensive, happens, Happenstance Income, Expenses, etc. If you
want to match pension, don't get lazy.