Multiple criterion including wildcards to sum in a range

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
One way


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



--
Regards,

Peo Sjoblom

(No private emails please)
 
JEH said:
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
Click to expand...
....

=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*" ?
 
JEH said:
Thank you very much. I would never have got there on my own!
Can D2 house a wildcard, such as "*pens*" ?
Click to expand...
....

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.
 
Back
Top