Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
=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. |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
"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))) |
#5
|
|||
|
|||
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. |
#6
|
|||
|
|||
"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. |
#7
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I get the mode, min, and max with multiple criteria? | Excel Discussion (Misc queries) | |||
adding certain cells in multiple worksheets in multiple workbooks | Excel Worksheet Functions | |||
XML / parent with multiple children and with multiple children | Excel Discussion (Misc queries) | |||
Multiple formulas including INDIRECT | Excel Worksheet Functions | |||
multiple entries | Excel Worksheet Functions |