"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)))
|