sumproduct - find text in string

BeSmart

unread,

Feb 22, 2010, 9:56:03 AM2/22/10

to

Hi all

I tried to write a sumproduct formula to find various text within a range
and then sum the totals for those rows found, but my attempts failed. If
anyone could suggest a formula - it would be very much appreciated:

Here’s what I need the formula to do:
The user enters strings of words into any cells within range(B37:H165)

I need the formula to look in range(B37:H165) and find any rows containing
strings of text that include the words "production" or "installation" or "non
commission".

For those rows found, I need the formula to add all values in the same rows
– but over in column CB (the 1st months total column) i.e.

Within range(B37:H165) the formula finds that the word “production” appears
within text entered into cell C40
“installation” appears within text entered into cell D51
“production” appears within text entered into cell B70
“non commission” appears within text entered into cell H150
The formula then goes to range(CB37:CB165) and sums the values in in the
rows found ie. =CB40+CB51+CB70+CB150

FYI - Columns “I” to “CA” contain general data.
FYI - Columns “CB” to “CG” contain monthly totals.

--
Thank in advance for your help
BeSmart

Gary''s Student

unread,

Feb 22, 2010, 10:24:01 AM2/22/10

to

Just to be sure we understand. If C40 contains "production" and D40 contains
"production" then should the formula be:
=CB40+CB40
or
=CB40
--
Gary''s Student - gsnu201001

Bernard Liengme

unread,

Feb 22, 2010, 10:56:40 AM2/22/10

to

Difficult to test with data in B37:H165
I entered data in A1:D13
Some cells have text that include one of: cat, dog, horse
Cells G1:G13 have numbers

The formula
=SUMPRODUCT(
(ISNUMBER(FIND("cat",A1:D13))+ISNUMBER(FIND("dog",A1:D13))+ISNUMBER(FIND("horse",A1:D13)))*G1:G13)
sum those G values that are in rows where a cell contains one of the words

Note, however, if (for example) A4 has CAT and D4 has DOG then G4 gets added
twice
To avoid this, use helper column - I used K1:K13
In K1:
=--(SUMPRODUCT(ISNUMBER(FIND("cat",A1:D1))+ISNUMBER(FIND("dog",A1:D1))+ISNUMBER(FIND("horse",A1:D1)))>0)
This is copied down the column
To find required sum: =SUMPRODUCT(G1:G13,K1:K13)
If required helper column (K) could be hidden
best wishes
--
Bernard Liengme
Microsoft Excel MVP
people.stfx.ca/bliengme
email address: remove uppercase characters

"BeSmart" <BeS...@discussions.microsoft.com> wrote in message
news:77BEA01B-EAB7-4042...@microsoft.com...

T. Valko

unread,

Feb 22, 2010, 4:38:32 PM2/22/10

to

Let's examine this simplified data set:

...Ax.....oB.....C.....3
...Xu.....T.....pA.....5
...B.....O.....Cy.....10
...Ti.....iA.....Ai.....8

You want to look for cells that contain A or B or C anywhere within the cell
and sum the corresponding value.

Based on that sample data what result do you expect? Would the correct
result be:

(3*3)+(1*5)+(2*10)+(2*8) = 50

Or:

3+5+10+8 = 26

--
Biff
Microsoft Excel MVP

"BeSmart" <BeS...@discussions.microsoft.com> wrote in message
news:77BEA01B-EAB7-4042...@microsoft.com...

> Hi all
>
> I tried to write a sumproduct formula to find various text within a range
> and then sum the totals for those rows found, but my attempts failed. If
> anyone could suggest a formula - it would be very much appreciated:
>
> Here's what I need the formula to do:
> The user enters strings of words into any cells within range(B37:H165)
>
> I need the formula to look in range(B37:H165) and find any rows containing
> strings of text that include the words "production" or "installation" or
> "non
> commission".
>
> For those rows found, I need the formula to add all values in the same
> rows

> - but over in column CB (the 1st months total column) i.e.

BeSmart

unread,

Feb 22, 2010, 7:04:05 PM2/22/10

to

Hi
Thanks heaps for your assistance & questions.

I need the row to be counted once if the word is found twice in the same row.
Although this is unlikely to happen as the other columns require information
not relating to my three words. However you never know with a user...

So to use Biff's example the calculation for his table would be:

....Ax.....oB.....C.....3
....Xu.....T.....pA.....5
....B.....O.....Cy.....10
....Ti.....iA.....Ai.....8

3+5+10+8=26

Perhaps the safest and simpliest way to do this (as Bernard suggested) would
be to have a hidden helper column? Or is there a way to avoid duplication
within the formula?

--
Thank for your help
BeSmart

"T. Valko" wrote:

> Let's examine this simplified data set:
>

> ....Ax.....oB.....C.....3
> ....Xu.....T.....pA.....5
> ....B.....O.....Cy.....10
> ....Ti.....iA.....Ai.....8

> .
>

T. Valko

unread,

Feb 22, 2010, 7:59:16 PM2/22/10

to

Try this...

Use cells to hold the criteria. Note: these cells *must* be in a horizontal
range.

A32 = production
B32 = installation
C32 = non commission

Then:

=SUMPRODUCT(--(MMULT(--(COUNTIF(OFFSET(B37:H165,ROW(B37:H165)-ROW(B37),,1),"*"&A32:C32&"*")>0),{1;1;1})>0),CB37:CB165)

--
Biff
Microsoft Excel MVP

"BeSmart" <BeS...@discussions.microsoft.com> wrote in message

news:DDC20A11-66EE-4A99...@microsoft.com...

T. Valko

unread,

Feb 22, 2010, 10:54:59 PM2/22/10

to

Minor tweak that saves a few keystrokes:

=SUMPRODUCT(--(MMULT(COUNTIF(OFFSET(B37:H165,ROW(B37:H165)-ROW(B37),,1),"*"&A32:C32&"*"),{1;1;1})>0),CB37:CB165)

--
Biff
Microsoft Excel MVP

"T. Valko" <biffi...@comcast.net> wrote in message
news:eSnmsNCt...@TK2MSFTNGP04.phx.gbl...

BeSmart

unread,

Feb 23, 2010, 12:55:01 AM2/23/10

to

Thanks Biff
It looks complicated (I'm not sure that I totally understand what it's doing
- but I get the general idea)
What really matters is that works brilliantly and helps me immensely!!!

Thank you very much for spending the time to help me!!
Cheers
BeSmart

T. Valko

unread,

Feb 23, 2010, 10:54:22 AM2/23/10

to

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

"BeSmart" <BeS...@discussions.microsoft.com> wrote in message

news:DA89FE15-9B84-49FB...@microsoft.com...

Kwok Ng

unread,

Jul 30, 2010, 4:36:26 AM7/30/10

to

Hi, I have tried to figure out what the code above does as this is similar to a problem that I am facing. I am using Excel 2003 and I have two columns with data on resources. Column F is the role name and Column H contains people names.

What I would like to know is how to count the number of specific role names that have an associated people name. The trick here is that the role names are all different and but that contain a certain string of text in the cell which makes them similar, in this example it is "field engineer". So far I have tried,

=SUMPRODUCT(--(F3:F659="field engineer"), --(H3:H659<>""))

Which returned zero results, then I tried:

=SUMPRODUCT(--(F3:F659=("*" & "field engineer" & "*")),--(H3:H659<>""))

Which also returned zero results.

Any help would be appreciated.

Thanks

T. Valko wrote:

You're welcome. Thanks for the feedback!--BiffMicrosoft Excel MVP

23-Feb-10

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

Previous Posts In This Thread:

On Monday, February 22, 2010 9:56 AM
BeSmart wrote:

sumproduct - find text in string
Hi all

I tried to write a sumproduct formula to find various text within a range
and then sum the totals for those rows found, but my attempts failed. If
anyone could suggest a formula - it would be very much appreciated:

Here???s what I need the formula to do:

The user enters strings of words into any cells within range(B37:H165)

I need the formula to look in range(B37:H165) and find any rows containing
strings of text that include the words "production" or "installation" or "non
commission".

For those rows found, I need the formula to add all values in the same rows

??? but over in column CB (the 1st months total column) i.e.

Within range(B37:H165) the formula finds that the word ???production??? appears

within text entered into cell C40

???installation??? appears within text entered into cell D51
???production??? appears within text entered into cell B70
???non commission??? appears within text entered into cell H150

The formula then goes to range(CB37:CB165) and sums the values in in the
rows found ie. =CB40+CB51+CB70+CB150

FYI - Columns ???I??? to ???CA??? contain general data.
FYI - Columns ???CB??? to ???CG??? contain monthly totals.

--
Thank in advance for your help
BeSmart

On Monday, February 22, 2010 10:24 AM
Gary''s Student wrote:

Just to be sure we understand.
Just to be sure we understand. If C40 contains "production" and D40 contains
"production" then should the formula be:
=CB40+CB40
or
=CB40
--
Gary''s Student - gsnu201001

"BeSmart" wrote:

On Monday, February 22, 2010 10:56 AM
Bernard Liengme wrote:

Difficult to test with data in B37:H165I entered data in A1:D13Some cells have
Difficult to test with data in B37:H165
I entered data in A1:D13
Some cells have text that include one of: cat, dog, horse
Cells G1:G13 have numbers

The formula
=SUMPRODUCT(
(ISNUMBER(FIND("cat",A1:D13))+ISNUMBER(FIND("dog",A1:D13))+ISNUMBER(FIND("horse",A1:D13)))*G1:G13)
sum those G values that are in rows where a cell contains one of the words

Note, however, if (for example) A4 has CAT and D4 has DOG then G4 gets added
twice
To avoid this, use helper column - I used K1:K13
In K1:
=--(SUMPRODUCT(ISNUMBER(FIND("cat",A1:D1))+ISNUMBER(FIND("dog",A1:D1))+ISNUMBER(FIND("horse",A1:D1)))>0)
This is copied down the column
To find required sum: =SUMPRODUCT(G1:G13,K1:K13)
If required helper column (K) could be hidden
best wishes
--
Bernard Liengme
Microsoft Excel MVP
people.stfx.ca/bliengme
email address: remove uppercase characters

On Monday, February 22, 2010 4:38 PM
T. Valko wrote:

Let's examine this simplified data set:...Ax.....oB.....C.....3...Xu.....T.....

Let's examine this simplified data set:

....Ax.....oB.....C.....3
....Xu.....T.....pA.....5
....B.....O.....Cy.....10
....Ti.....iA.....Ai.....8

You want to look for cells that contain A or B or C anywhere within the cell
and sum the corresponding value.

Based on that sample data what result do you expect? Would the correct
result be:

(3*3)+(1*5)+(2*10)+(2*8) = 50

Or:

3+5+10+8 = 26

--
Biff
Microsoft Excel MVP

On Monday, February 22, 2010 7:04 PM
BeSmart wrote:

HiThanks heaps for your assistance & questions.

Hi
Thanks heaps for your assistance & questions.

I need the row to be counted once if the word is found twice in the same row.
Although this is unlikely to happen as the other columns require information
not relating to my three words. However you never know with a user...

So to use Biff's example the calculation for his table would be:

.....Ax.....oB.....C.....3
.....Xu.....T.....pA.....5
.....B.....O.....Cy.....10
.....Ti.....iA.....Ai.....8

3+5+10+8=26

Perhaps the safest and simpliest way to do this (as Bernard suggested) would
be to have a hidden helper column? Or is there a way to avoid duplication
within the formula?

--
Thank for your help
BeSmart

"T. Valko" wrote:

On Monday, February 22, 2010 7:59 PM
T. Valko wrote:

Try this...Use cells to hold the criteria.
Try this...

Use cells to hold the criteria. Note: these cells *must* be in a horizontal
range.

A32 = production
B32 = installation
C32 = non commission

Then:

=SUMPRODUCT(--(MMULT(--(COUNTIF(OFFSET(B37:H165,ROW(B37:H165)-ROW(B37),,1),"*"&A32:C32&"*")>0),{1;1;1})>0),CB37:CB165)

--
Biff
Microsoft Excel MVP

On Monday, February 22, 2010 10:54 PM
T. Valko wrote:

Minor tweak that saves a few
Minor tweak that saves a few keystrokes:

=SUMPRODUCT(--(MMULT(COUNTIF(OFFSET(B37:H165,ROW(B37:H165)-ROW(B37),,1),"*"&A32:C32&"*"),{1;1;1})>0),CB37:CB165)

--
Biff
Microsoft Excel MVP

On Tuesday, February 23, 2010 12:55 AM
BeSmart wrote:

Thanks BiffIt looks complicated (I am not sure that I totally understand what
Thanks Biff
It looks complicated (I am not sure that I totally understand what it is doing

- but I get the general idea)
What really matters is that works brilliantly and helps me immensely!!!

Thank you very much for spending the time to help me!!
Cheers
BeSmart

On Tuesday, February 23, 2010 10:54 AM
T. Valko wrote:

You're welcome. Thanks for the feedback!--BiffMicrosoft Excel MVP
You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

Submitted via EggHeadCafe - Software Developer Portal of Choice
Store ASP.NET Site Visitor Stats in MongoDb
http://www.eggheadcafe.com/tutorials/aspnet/3a73c6de-82a1-4690-a7aa-d0eda58203f7/store-aspnet-site-visitor-stats-in-mongodb.aspx

Pete_UK

unread,

Jul 30, 2010, 7:49:06 AM7/30/10

to

You can't use wildcards in that way. Try it like this:

=SUMPRODUCT(--ISNUMBER(SEARCH("field engineer",F3:F659)),--
(H3:H659<>""))

Hope this helps.

Pete

> (ISNUMBER(FIND("cat",A1:D13))+ISNUMBER(FIND("dog",A1:D13))+ISNUMBER(FIND("horse",A1:D13)))*G1:G13)

> sum those G values that are in rows where a cell contains one of the words
>
> Note, however, if (for example) A4 has CAT and D4 has DOG then G4 gets added
> twice
> To avoid this, use helper column - I used K1:K13
> In K1:

> =--(SUMPRODUCT(ISNUMBER(FIND("cat",A1:D1))+ISNUMBER(FIND("dog",A1:D1))+ISNUMBER(FIND("horse",A1:D1)))>0)

> Store ASP.NET Site Visitor Stats in MongoDbhttp://www.eggheadcafe.com/tutorials/aspnet/3a73c6de-82a1-4690-a7aa-d...