Using SUMIF on common words
I would like to use the SUMIF function to add up cells containing common
words, but not necessarily the same word, i.e. Cell1 contains party123 value 100 Cell2 contains party 456 value 50 Cell3 contains poser 122 value 100 Cell4 conatins partypooper value 100 Therefore total cells containing "party" = cell 1+cell 2 +cell 4 = 250 Is it possible to do this? |
Is your data laid out nicely in columns (like descriptions in A1:A10 and values
in B1:B10)? If yes: =SUMPRODUCT(--ISNUMBER(SEARCH("party",A1:A10,1)),B1:B10) Dodge wrote: I would like to use the SUMIF function to add up cells containing common words, but not necessarily the same word, i.e. Cell1 contains party123 value 100 Cell2 contains party 456 value 50 Cell3 contains poser 122 value 100 Cell4 conatins partypooper value 100 Therefore total cells containing "party" = cell 1+cell 2 +cell 4 = 250 Is it possible to do this? -- Dave Peterson |
Try this ARRAY formula which must be entered/edited using ctrl+shift+enter
=SUM(IF(ISERR(FIND("party",A3:A6)),"",VALUE(TRIM(R IGHT(A3:A6,LEN(A3:A6)-FIND ("value",A3:A6)-4))))) -- Don Guillett SalesAid Software "Dodge" wrote in message ... I would like to use the SUMIF function to add up cells containing common words, but not necessarily the same word, i.e. Cell1 contains party123 value 100 Cell2 contains party 456 value 50 Cell3 contains poser 122 value 100 Cell4 conatins partypooper value 100 Therefore total cells containing "party" = cell 1+cell 2 +cell 4 = 250 Is it possible to do this? |
use the DSUM function
if your data headings are [Name] and [Value] Criteria will be Name Value party this works.. "Dodge" wrote: I would like to use the SUMIF function to add up cells containing common words, but not necessarily the same word, i.e. Cell1 contains party123 value 100 Cell2 contains party 456 value 50 Cell3 contains poser 122 value 100 Cell4 conatins partypooper value 100 Therefore total cells containing "party" = cell 1+cell 2 +cell 4 = 250 Is it possible to do this? |
=sumif("party*",range:range,1)
"Dodge" wrote: I would like to use the SUMIF function to add up cells containing common words, but not necessarily the same word, i.e. Cell1 contains party123 value 100 Cell2 contains party 456 value 50 Cell3 contains poser 122 value 100 Cell4 conatins partypooper value 100 Therefore total cells containing "party" = cell 1+cell 2 +cell 4 = 250 Is it possible to do this? |
Maybe this...
=SUMIF(A1:A10,"party*",B1:B10) or =SUMIF(A1:A10,"*party*",B1:B10) cisse_5 wrote: =sumif("party*",range:range,1) "Dodge" wrote: I would like to use the SUMIF function to add up cells containing common words, but not necessarily the same word, i.e. Cell1 contains party123 value 100 Cell2 contains party 456 value 50 Cell3 contains poser 122 value 100 Cell4 conatins partypooper value 100 Therefore total cells containing "party" = cell 1+cell 2 +cell 4 = 250 Is it possible to do this? -- Dave Peterson |
All times are GMT +1. The time now is 01:15 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com