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:

Heres 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

sumproduct - find text in string
 

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:

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:

Heres 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

sumproduct - find text in string
 

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(F IND("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" wrote in message
...
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:

Heres 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

sumproduct - find text in string
 

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" wrote in message
...
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

sumproduct - find text in string
 

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

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" wrote in message
...
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


.

sumproduct - find text in string
 

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" wrote in message
...
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

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" wrote in message
...
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


.

sumproduct - find text in string
 

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:CB1 65)

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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" wrote in message
...
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

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" wrote in message
...
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


.

sumproduct - find text in string
 

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

sumproduct - find text in string
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"BeSmart" wrote in message
...
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