Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
string lookup combined with SUM
Hi, can someone offer any suggestions on how to add up some values based on a
column that contains a word that I specify. For example: Brown Cat 2 Brown Dog 3 Tabby Cat 2 Red Dog 4 What function can i use to add up on the those columns that contain "cat"? Thanks in advance. DP |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
string lookup combined with SUM
Try this:
=SUMPRODUCT((ISNUMBER(SEARCH("cat",A1:A4)))*(B1:B4 )) this adds up the rows that contain cat. Hope this helps. Pete On Jul 2, 6:50*pm, bestrugger wrote: Hi, can someone offer any suggestions on how to add up some values based on a column that contains a word that I specify. *For example: Brown Cat * 2 Brown Dog *3 Tabby Cat * 2 Red Dog * * *4 What function can i use to add up on the those columns that contain "cat"? * Thanks in advance. DP |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
string lookup combined with SUM
Assuming that there is always a description followed by a space and then the
word cat: =SUMIF(A1:A10,"* cat",B1:B10) Or, use a cell to hold the word cat: D1 = cat =SUMIF(A1:A4,"* "&D1,B1:B4) You could also use: =SUMIF(A1:A4,"*cat",B1:B4) Or: =SUMIF(A1:A4,"*cat*",B1:B4) But these are more prone to "false positives". So, which is best depends on the variety of entries that you have. -- Biff Microsoft Excel MVP "bestrugger" wrote in message ... Hi, can someone offer any suggestions on how to add up some values based on a column that contains a word that I specify. For example: Brown Cat 2 Brown Dog 3 Tabby Cat 2 Red Dog 4 What function can i use to add up on the those columns that contain "cat"? Thanks in advance. DP |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
string lookup combined with SUM
You're awesome. Works like a champ!!!!!!!
"Pete_UK" wrote: Try this: =SUMPRODUCT((ISNUMBER(SEARCH("cat",A1:A4)))*(B1:B4 )) this adds up the rows that contain cat. Hope this helps. Pete On Jul 2, 6:50 pm, bestrugger wrote: Hi, can someone offer any suggestions on how to add up some values based on a column that contains a word that I specify. For example: Brown Cat 2 Brown Dog 3 Tabby Cat 2 Red Dog 4 What function can i use to add up on the those columns that contain "cat"? Thanks in advance. DP |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
string lookup combined with SUM
Well, thanks for feeding back.
Pete On Jul 2, 7:31*pm, bestrugger wrote: You're awesome. *Works like a champ!!!!!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
lookup a value within a text string | Excel Worksheet Functions | |||
IF and lookup functions combined? | Excel Worksheet Functions | |||
IF and lookup functions combined? | Excel Worksheet Functions | |||
Array formula combined with Lookup | Excel Discussion (Misc queries) | |||
Lookup the month in a date string 01/03/05 | Excel Worksheet Functions |