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

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

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

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

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!!!!!!!