sumproduct - find text in string

[BeSmart]

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.

 

[Gary''s Student]

Just to be sure we understand. If C40 contains "production" and D40 contains
"production" then should the formula be:
=CB40+CB40
or
=CB40

 

[Bernard Liengme]

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

The formula
=SUMPRODUCT(
(ISNUMBER(FIND("cat",A113))+ISNUMBER(FIND("dog",A113))+ISNUMBER(FIND("horse",A113)))*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",A11))+ISNUMBER(FIND("dog",A11))+ISNUMBER(FIND("horse",A11)))>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

 

[T. Valko]

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

 

[BeSmart]

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?

 

[T. Valko]

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)

 

[T. Valko]

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)

 

[BeSmart]

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]

You're welcome. Thanks for the feedback!

 

[Kwok Ng]

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

-
Bif
Microsoft Excel MVP

Previous Posts In This Thread:

sumproduct - find text in string
Hi al

I tried to write a sumproduct formula to find various text within a rang
and then sum the totals for those rows found, but my attempts failed. I
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 containin
strings of text that include the words "production" or "installation" or "no
commission"

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

Within range(B37:H165) the formula finds that the word ???production??? appear
within text entered into cell C4
???installation??? appears within text entered into cell D5
???production??? appears within text entered into cell B7
???non commission??? appears within text entered into cell H15
The formula then goes to range(CB37:CB165) and sums the values in in th
rows found ie. =CB40+CB51+CB70+CB15

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

-
Thank in advance for your hel
BeSmart

Just to be sure we understand.
Just to be sure we understand. If C40 contains "production" and D40 contain
"production" then should the formula be
=CB40+CB4
o
=CB4
-
Gary''s Student - gsnu20100

:

Difficult to test with data in B37:H165I entered data in A113Some cells have
Difficult to test with data in B37:H16
I entered data in A11
Some cells have text that include one of: cat, dog, hors
Cells G1:G13 have number

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

Note, however, if (for example) A4 has CAT and D4 has DOG then G4 gets adde
twic
To avoid this, use helper column - I used K1:K1
In K1
=--(SUMPRODUCT(ISNUMBER(FIND("cat",A11))+ISNUMBER(FIND("dog",A11))+ISNUMBER(FIND("horse",A11)))>0
This is copied down the colum
To find required sum: =SUMPRODUCT(G1:G13,K1:K13
If required helper column (K) could be hidde
best wishe
-
Bernard Liengm
Microsoft Excel MV
people.stfx.ca/bliengm
email address: remove uppercase characters

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

....Ax.....oB.....C.....
....Xu.....T.....pA.....
....B.....O.....Cy.....1
....Ti.....iA.....Ai.....

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

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

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

Or:

3+5+10+8 = 26

--
Biff
Microsoft Excel MVP

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


:

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

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

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

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/tutorial...ore-aspnet-site-visitor-stats-in-mongodb.aspx

 

[Pete_UK]

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

 

[Arjun]

Ng

Ng,
It would be easier to use a sum, offset & Match function formula. Match field engineer and offset to x columns to the right and sum.

One of the problems with the formula you are using is that it gives a count of field engineer and not the sum of the non blank rows associated with the field engineer record. The other problem is that isblank function does not work either. Nulls and blanks are hard to work with anyways.

I do not know sumproduct that well.

Arjun