Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11
Default SUMIF or SUMPRODUCT to total cells containing multiple texts

I am attempting to set up a spreadsheet whereby I can summarise the weight
load of a vehicle. The cells will contain a variation of abbreviated texts
in the same cell e.g.

An example of column B contents is ah, zy, pd, (in a single cell on each row)

Each abbreviation will have it's own unique number (weight) allocated in two
seperate column which the formula will refer to - i.e

Column IU IV
Row 1 ah 2.2
Row 2 zy 3.7
Row 3 pd 1.4

There will be approximately 50 such text references and values.

Currently column b (rows 1 to 50) contain the varied text (ah, zy, pd etc in
a single cell). Is it possible to 'find' the idividual text abbreviation
(ah, zy or pd) in column B and total the allocated weight using sumif or
sumproduct even though they are in the same cell?

Any suggestions greatly appreciated.
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,886
Default SUMIF or SUMPRODUCT to total cells containing multiple texts

Hi

How many two letter combinations are there?
Does the cell in column B always contain 3 sets of letters, or, can
there be 1, 2, 3 (or more) sets?

I would be inclined (subject to the answers to the above) to create a
further series of entries in IU and IV
ah zy 5.9
zy ah 5.9
ah pd 3.6
pd ah 3.6
ah zy pd 7.3
plus all the other combinations

Then use
=VLOOKUP(B1,IU:IV,2,0)

--
Regards

Roger Govier


"Terranoman" wrote in message
...
I am attempting to set up a spreadsheet whereby I can summarise the
weight
load of a vehicle. The cells will contain a variation of abbreviated
texts
in the same cell e.g.

An example of column B contents is ah, zy, pd, (in a single cell on
each row)

Each abbreviation will have it's own unique number (weight) allocated
in two
seperate column which the formula will refer to - i.e

Column IU IV
Row 1 ah 2.2
Row 2 zy 3.7
Row 3 pd 1.4

There will be approximately 50 such text references and values.

Currently column b (rows 1 to 50) contain the varied text (ah, zy, pd
etc in
a single cell). Is it possible to 'find' the idividual text
abbreviation
(ah, zy or pd) in column B and total the allocated weight using sumif
or
sumproduct even though they are in the same cell?

Any suggestions greatly appreciated.



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11
Default SUMIF or SUMPRODUCT to total cells containing multiple texts

There can be multiple combinations, there could be a minimum of 3 up to a
maximum of 15 in the cell, but the combinations will not always be the same
(this is dependant on the items being shipped).

Thanks



"Roger Govier" wrote:

Hi

How many two letter combinations are there?
Does the cell in column B always contain 3 sets of letters, or, can
there be 1, 2, 3 (or more) sets?

I would be inclined (subject to the answers to the above) to create a
further series of entries in IU and IV
ah zy 5.9
zy ah 5.9
ah pd 3.6
pd ah 3.6
ah zy pd 7.3
plus all the other combinations

Then use
=VLOOKUP(B1,IU:IV,2,0)

--
Regards

Roger Govier


"Terranoman" wrote in message
...
I am attempting to set up a spreadsheet whereby I can summarise the
weight
load of a vehicle. The cells will contain a variation of abbreviated
texts
in the same cell e.g.

An example of column B contents is ah, zy, pd, (in a single cell on
each row)

Each abbreviation will have it's own unique number (weight) allocated
in two
seperate column which the formula will refer to - i.e

Column IU IV
Row 1 ah 2.2
Row 2 zy 3.7
Row 3 pd 1.4

There will be approximately 50 such text references and values.

Currently column b (rows 1 to 50) contain the varied text (ah, zy, pd
etc in
a single cell). Is it possible to 'find' the idividual text
abbreviation
(ah, zy or pd) in column B and total the allocated weight using sumif
or
sumproduct even though they are in the same cell?

Any suggestions greatly appreciated.




  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,886
Default SUMIF or SUMPRODUCT to total cells containing multiple texts

Hi

If that's the case, then other than some VBA code to carry out the task,
I would do all the calculation on a separate sheet.
Copy Columns IU and IV to columns A and B of sheet2.
Assuming your entry with the strings of 2 characters is in column A of
Sheet1, then in cell C1 of Sheet2 enter

=IF(Sheet1!A1="","",MID(Sheet1!$A1,1+(COLUMN(A1)-1)*3,2))
copy across through D1:R1
In cell S1 of Sheet2 enter
=IF(C1="","",VLOOKUP(C1,$A:$B,2,0))
Copy across through cells T1:AJ1
in cell AK1
=SUM(S1:AJ1)
Copy C1:AK1 down as far as required

On sheet1 in cell B1 to calculate the total weight, use
=Sheet2!AK1

--
Regards

Roger Govier


"Terranoman" wrote in message
...
There can be multiple combinations, there could be a minimum of 3 up
to a
maximum of 15 in the cell, but the combinations will not always be the
same
(this is dependant on the items being shipped).

Thanks



"Roger Govier" wrote:

Hi

How many two letter combinations are there?
Does the cell in column B always contain 3 sets of letters, or, can
there be 1, 2, 3 (or more) sets?

I would be inclined (subject to the answers to the above) to create a
further series of entries in IU and IV
ah zy 5.9
zy ah 5.9
ah pd 3.6
pd ah 3.6
ah zy pd 7.3
plus all the other combinations

Then use
=VLOOKUP(B1,IU:IV,2,0)

--
Regards

Roger Govier


"Terranoman" wrote in message
...
I am attempting to set up a spreadsheet whereby I can summarise the
weight
load of a vehicle. The cells will contain a variation of
abbreviated
texts
in the same cell e.g.

An example of column B contents is ah, zy, pd, (in a single cell
on
each row)

Each abbreviation will have it's own unique number (weight)
allocated
in two
seperate column which the formula will refer to - i.e

Column IU IV
Row 1 ah 2.2
Row 2 zy 3.7
Row 3 pd 1.4

There will be approximately 50 such text references and values.

Currently column b (rows 1 to 50) contain the varied text (ah, zy,
pd
etc in
a single cell). Is it possible to 'find' the idividual text
abbreviation
(ah, zy or pd) in column B and total the allocated weight using
sumif
or
sumproduct even though they are in the same cell?

Any suggestions greatly appreciated.






  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10,593
Default SUMIF or SUMPRODUCT to total cells containing multiple texts

I managed it with a formula that basically looks like this assuming the data
is in B2

=SUM(TRANSPOSE(OFFSET($V$1,TRANSPOSE(
IF(ISNUMBER(MATCH(MID(SUBSTITUTE(Sheet2!B2,",","") ,ROW(INDIRECT("1:"&LEN(SUB
STITUTE(Sheet2!B2,",","")))),2),Sheet2!$U$1:$U$50, 0)),
MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIREC T("1:"&LEN(SUBSTITUTE(Shee
t2!B2,",","")))),2),Sheet2!$U$1:$U$50,0),1))-1,0)))

Unfortunately, that has too many nested functions, so I had to split out

MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIREC T("1:"&LEN(SUBSTITUTE(Shee
t2!B2,",","")))),2),Sheet2!$U$1:$U$50,0)

and define it as a name (Ctrl-F3). I used a name of matches and a Refersto
value of

=MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIRE CT("1:"&LEN(SUBSTITUTE(She
et2!B2,",","")))),2),Sheet2!$U$1:$U$50,0)

and then my formula is

=SUM(TRANSPOSE(OFFSET($V$1,TRANSPOSE(IF(ISNUMBER(m atches),matches,1))-1,0)))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

I also had to enter a dummy value in row 1 of your lookup table with a
weight of 0 to get it to work.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Terranoman" wrote in message
...
I am attempting to set up a spreadsheet whereby I can summarise the weight
load of a vehicle. The cells will contain a variation of abbreviated

texts
in the same cell e.g.

An example of column B contents is ah, zy, pd, (in a single cell on each

row)

Each abbreviation will have it's own unique number (weight) allocated in

two
seperate column which the formula will refer to - i.e

Column IU IV
Row 1 ah 2.2
Row 2 zy 3.7
Row 3 pd 1.4

There will be approximately 50 such text references and values.

Currently column b (rows 1 to 50) contain the varied text (ah, zy, pd etc

in
a single cell). Is it possible to 'find' the idividual text abbreviation
(ah, zy or pd) in column B and total the allocated weight using sumif or
sumproduct even though they are in the same cell?

Any suggestions greatly appreciated.





  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,886
Default SUMIF or SUMPRODUCT to total cells containing multiple texts

Hi Bob

Phhhewwww.....
That's great.
Just a very small point, the OP was using IU and IV for his columns for
the lookup table, not U and I and he did not say he was using Sheet2. I
used a second sheet in my suggestion.
As long as he makes those minor mods, he will have a superb solution.

I had totally missed that there were comma's separating the values in
the source cell in my rather mundane solution which would necessitate a
change to the formula to
=IF(Sheet1!A1="","",MID(Sheet1!$A1,1+(COLUMN(A1)-1)*4,2))

I think he should go with your solution.

--
Regards

Roger Govier


"Bob Phillips" wrote in message
...
I managed it with a formula that basically looks like this assuming the
data
is in B2

=SUM(TRANSPOSE(OFFSET($V$1,TRANSPOSE(
IF(ISNUMBER(MATCH(MID(SUBSTITUTE(Sheet2!B2,",","") ,ROW(INDIRECT("1:"&LEN(SUB
STITUTE(Sheet2!B2,",","")))),2),Sheet2!$U$1:$U$50, 0)),
MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIREC T("1:"&LEN(SUBSTITUTE(Shee
t2!B2,",","")))),2),Sheet2!$U$1:$U$50,0),1))-1,0)))

Unfortunately, that has too many nested functions, so I had to split
out

MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIREC T("1:"&LEN(SUBSTITUTE(Shee
t2!B2,",","")))),2),Sheet2!$U$1:$U$50,0)

and define it as a name (Ctrl-F3). I used a name of matches and a
Refersto
value of

=MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIRE CT("1:"&LEN(SUBSTITUTE(She
et2!B2,",","")))),2),Sheet2!$U$1:$U$50,0)

and then my formula is

=SUM(TRANSPOSE(OFFSET($V$1,TRANSPOSE(IF(ISNUMBER(m atches),matches,1))-1,0)))

which is an array formula, it should be committed with
Ctrl-Shift-Enter, not
just Enter.

I also had to enter a dummy value in row 1 of your lookup table with a
weight of 0 to get it to work.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Terranoman" wrote in message
...
I am attempting to set up a spreadsheet whereby I can summarise the
weight
load of a vehicle. The cells will contain a variation of abbreviated

texts
in the same cell e.g.

An example of column B contents is ah, zy, pd, (in a single cell on
each

row)

Each abbreviation will have it's own unique number (weight) allocated
in

two
seperate column which the formula will refer to - i.e

Column IU IV
Row 1 ah 2.2
Row 2 zy 3.7
Row 3 pd 1.4

There will be approximately 50 such text references and values.

Currently column b (rows 1 to 50) contain the varied text (ah, zy, pd
etc

in
a single cell). Is it possible to 'find' the idividual text
abbreviation
(ah, zy or pd) in column B and total the allocated weight using sumif
or
sumproduct even though they are in the same cell?

Any suggestions greatly appreciated.





  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10,593
Default SUMIF or SUMPRODUCT to total cells containing multiple texts

Sorry about the IU IV, I built it using U and V as I could see those
columns.

Personally, I think he should re-design his spreadsheet <bg

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Roger Govier" wrote in message
...
Hi Bob

Phhhewwww.....
That's great.
Just a very small point, the OP was using IU and IV for his columns for
the lookup table, not U and I and he did not say he was using Sheet2. I
used a second sheet in my suggestion.
As long as he makes those minor mods, he will have a superb solution.

I had totally missed that there were comma's separating the values in
the source cell in my rather mundane solution which would necessitate a
change to the formula to
=IF(Sheet1!A1="","",MID(Sheet1!$A1,1+(COLUMN(A1)-1)*4,2))

I think he should go with your solution.

--
Regards

Roger Govier


"Bob Phillips" wrote in message
...
I managed it with a formula that basically looks like this assuming the
data
is in B2

=SUM(TRANSPOSE(OFFSET($V$1,TRANSPOSE(

IF(ISNUMBER(MATCH(MID(SUBSTITUTE(Sheet2!B2,",","") ,ROW(INDIRECT("1:"&LEN(SUB
STITUTE(Sheet2!B2,",","")))),2),Sheet2!$U$1:$U$50, 0)),

MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIREC T("1:"&LEN(SUBSTITUTE(Shee
t2!B2,",","")))),2),Sheet2!$U$1:$U$50,0),1))-1,0)))

Unfortunately, that has too many nested functions, so I had to split
out


MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIREC T("1:"&LEN(SUBSTITUTE(Shee
t2!B2,",","")))),2),Sheet2!$U$1:$U$50,0)

and define it as a name (Ctrl-F3). I used a name of matches and a
Refersto
value of


=MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIRE CT("1:"&LEN(SUBSTITUTE(She
et2!B2,",","")))),2),Sheet2!$U$1:$U$50,0)

and then my formula is


=SUM(TRANSPOSE(OFFSET($V$1,TRANSPOSE(IF(ISNUMBER(m atches),matches,1))-1,0)))

which is an array formula, it should be committed with
Ctrl-Shift-Enter, not
just Enter.

I also had to enter a dummy value in row 1 of your lookup table with a
weight of 0 to get it to work.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Terranoman" wrote in message
...
I am attempting to set up a spreadsheet whereby I can summarise the
weight
load of a vehicle. The cells will contain a variation of abbreviated

texts
in the same cell e.g.

An example of column B contents is ah, zy, pd, (in a single cell on
each

row)

Each abbreviation will have it's own unique number (weight) allocated
in

two
seperate column which the formula will refer to - i.e

Column IU IV
Row 1 ah 2.2
Row 2 zy 3.7
Row 3 pd 1.4

There will be approximately 50 such text references and values.

Currently column b (rows 1 to 50) contain the varied text (ah, zy, pd
etc

in
a single cell). Is it possible to 'find' the idividual text
abbreviation
(ah, zy or pd) in column B and total the allocated weight using sumif
or
sumproduct even though they are in the same cell?

Any suggestions greatly appreciated.







  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,886
Default SUMIF or SUMPRODUCT to total cells containing multiple texts

Personally, I think he should re-design his spreadsheet <bg

Well, funnily enough, that thought also crossed my mind<g
but seeing as he was using the last 2 columns, I wondered whether it was
so horrendous that all columns were being used, and the values couldn't
be split out into other columns. That's why I opted for using 2nd sheet.

--
Regards

Roger Govier


"Bob Phillips" wrote in message
...
Sorry about the IU IV, I built it using U and V as I could see those
columns.

Personally, I think he should re-design his spreadsheet <bg

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Roger Govier" wrote in message
...
Hi Bob

Phhhewwww.....
That's great.
Just a very small point, the OP was using IU and IV for his columns
for
the lookup table, not U and I and he did not say he was using Sheet2.
I
used a second sheet in my suggestion.
As long as he makes those minor mods, he will have a superb solution.

I had totally missed that there were comma's separating the values in
the source cell in my rather mundane solution which would necessitate
a
change to the formula to
=IF(Sheet1!A1="","",MID(Sheet1!$A1,1+(COLUMN(A1)-1)*4,2))

I think he should go with your solution.

--
Regards

Roger Govier


"Bob Phillips" wrote in message
...
I managed it with a formula that basically looks like this assuming
the
data
is in B2

=SUM(TRANSPOSE(OFFSET($V$1,TRANSPOSE(

IF(ISNUMBER(MATCH(MID(SUBSTITUTE(Sheet2!B2,",","") ,ROW(INDIRECT("1:"&LEN(SUB
STITUTE(Sheet2!B2,",","")))),2),Sheet2!$U$1:$U$50, 0)),

MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIREC T("1:"&LEN(SUBSTITUTE(Shee
t2!B2,",","")))),2),Sheet2!$U$1:$U$50,0),1))-1,0)))

Unfortunately, that has too many nested functions, so I had to
split
out


MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIREC T("1:"&LEN(SUBSTITUTE(Shee
t2!B2,",","")))),2),Sheet2!$U$1:$U$50,0)

and define it as a name (Ctrl-F3). I used a name of matches and a
Refersto
value of


=MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIRE CT("1:"&LEN(SUBSTITUTE(She
et2!B2,",","")))),2),Sheet2!$U$1:$U$50,0)

and then my formula is


=SUM(TRANSPOSE(OFFSET($V$1,TRANSPOSE(IF(ISNUMBER(m atches),matches,1))-1,0)))

which is an array formula, it should be committed with
Ctrl-Shift-Enter, not
just Enter.

I also had to enter a dummy value in row 1 of your lookup table
with a
weight of 0 to get it to work.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Terranoman" wrote in
message
...
I am attempting to set up a spreadsheet whereby I can summarise
the
weight
load of a vehicle. The cells will contain a variation of
abbreviated
texts
in the same cell e.g.

An example of column B contents is ah, zy, pd, (in a single cell
on
each
row)

Each abbreviation will have it's own unique number (weight)
allocated
in
two
seperate column which the formula will refer to - i.e

Column IU IV
Row 1 ah 2.2
Row 2 zy 3.7
Row 3 pd 1.4

There will be approximately 50 such text references and values.

Currently column b (rows 1 to 50) contain the varied text (ah, zy,
pd
etc
in
a single cell). Is it possible to 'find' the idividual text
abbreviation
(ah, zy or pd) in column B and total the allocated weight using
sumif
or
sumproduct even though they are in the same cell?

Any suggestions greatly appreciated.








  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10,593
Default SUMIF or SUMPRODUCT to total cells containing multiple texts

In this case Roger, I think it needs a BIG re-design. I tried to concoct a
formula where the values were not comma separated in one cell, but many
cells, and it wasn't much better.

This took me a while to figure out, I wouldn't want to come to this
spreadsheet and see that monster unprepared, so I think the maintenance is
probably not worth it.

Start again with the requirement, and come up with a simpler design. Often
works for me.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Roger Govier" wrote in message
...
Personally, I think he should re-design his spreadsheet <bg


Well, funnily enough, that thought also crossed my mind<g
but seeing as he was using the last 2 columns, I wondered whether it was
so horrendous that all columns were being used, and the values couldn't
be split out into other columns. That's why I opted for using 2nd sheet.

--
Regards

Roger Govier


"Bob Phillips" wrote in message
...
Sorry about the IU IV, I built it using U and V as I could see those
columns.

Personally, I think he should re-design his spreadsheet <bg

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Roger Govier" wrote in message
...
Hi Bob

Phhhewwww.....
That's great.
Just a very small point, the OP was using IU and IV for his columns
for
the lookup table, not U and I and he did not say he was using Sheet2.
I
used a second sheet in my suggestion.
As long as he makes those minor mods, he will have a superb solution.

I had totally missed that there were comma's separating the values in
the source cell in my rather mundane solution which would necessitate
a
change to the formula to
=IF(Sheet1!A1="","",MID(Sheet1!$A1,1+(COLUMN(A1)-1)*4,2))

I think he should go with your solution.

--
Regards

Roger Govier


"Bob Phillips" wrote in message
...
I managed it with a formula that basically looks like this assuming
the
data
is in B2

=SUM(TRANSPOSE(OFFSET($V$1,TRANSPOSE(


IF(ISNUMBER(MATCH(MID(SUBSTITUTE(Sheet2!B2,",","") ,ROW(INDIRECT("1:"&LEN(SUB
STITUTE(Sheet2!B2,",","")))),2),Sheet2!$U$1:$U$50, 0)),


MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIREC T("1:"&LEN(SUBSTITUTE(Shee
t2!B2,",","")))),2),Sheet2!$U$1:$U$50,0),1))-1,0)))

Unfortunately, that has too many nested functions, so I had to
split
out



MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIREC T("1:"&LEN(SUBSTITUTE(Shee
t2!B2,",","")))),2),Sheet2!$U$1:$U$50,0)

and define it as a name (Ctrl-F3). I used a name of matches and a
Refersto
value of



=MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIRE CT("1:"&LEN(SUBSTITUTE(She
et2!B2,",","")))),2),Sheet2!$U$1:$U$50,0)

and then my formula is



=SUM(TRANSPOSE(OFFSET($V$1,TRANSPOSE(IF(ISNUMBER(m atches),matches,1))-1,0)))

which is an array formula, it should be committed with
Ctrl-Shift-Enter, not
just Enter.

I also had to enter a dummy value in row 1 of your lookup table
with a
weight of 0 to get it to work.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Terranoman" wrote in
message
...
I am attempting to set up a spreadsheet whereby I can summarise
the
weight
load of a vehicle. The cells will contain a variation of
abbreviated
texts
in the same cell e.g.

An example of column B contents is ah, zy, pd, (in a single cell
on
each
row)

Each abbreviation will have it's own unique number (weight)
allocated
in
two
seperate column which the formula will refer to - i.e

Column IU IV
Row 1 ah 2.2
Row 2 zy 3.7
Row 3 pd 1.4

There will be approximately 50 such text references and values.

Currently column b (rows 1 to 50) contain the varied text (ah, zy,
pd
etc
in
a single cell). Is it possible to 'find' the idividual text
abbreviation
(ah, zy or pd) in column B and total the allocated weight using
sumif
or
sumproduct even though they are in the same cell?

Any suggestions greatly appreciated.










  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 605
Default SUMIF or SUMPRODUCT to total cells containing multiple texts

Again, I feel like thinking aloud even though I don't have a clue of what's going on.

Personally, I think he should re-design his spreadsheet <bg


That was the first thought that came to my mind when I briefly read the post. <g

Unfortunately I've inherited the spreadsheet and attempting to make it work

(without the use of a calculator and pen)....... You guys are way beyond my excel knowledge.

I feel so sorry that Terranoman has to put up with the inheritance.

Bob and Roger, I just want to point out this is exactly why I always focus on and have so many questions on speed etc. I want to learn to do it right (i.e. design the spreadsheet properly) in my early stage of learning so that I can save myself and others from pain and suffering when it comes to composing formulae down the road. I hope you understand my reasons behind my numerous questions. Your support and guidance are always appreciated.

Epinn

"Roger Govier" wrote in message ...
Personally, I think he should re-design his spreadsheet <bg


Well, funnily enough, that thought also crossed my mind<g
but seeing as he was using the last 2 columns, I wondered whether it was
so horrendous that all columns were being used, and the values couldn't
be split out into other columns. That's why I opted for using 2nd sheet.

--
Regards

Roger Govier


"Bob Phillips" wrote in message
...
Sorry about the IU IV, I built it using U and V as I could see those
columns.

Personally, I think he should re-design his spreadsheet <bg

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Roger Govier" wrote in message
...
Hi Bob

Phhhewwww.....
That's great.
Just a very small point, the OP was using IU and IV for his columns
for
the lookup table, not U and I and he did not say he was using Sheet2.
I
used a second sheet in my suggestion.
As long as he makes those minor mods, he will have a superb solution.

I had totally missed that there were comma's separating the values in
the source cell in my rather mundane solution which would necessitate
a
change to the formula to
=IF(Sheet1!A1="","",MID(Sheet1!$A1,1+(COLUMN(A1)-1)*4,2))

I think he should go with your solution.

--
Regards

Roger Govier


"Bob Phillips" wrote in message
...
I managed it with a formula that basically looks like this assuming
the
data
is in B2

=SUM(TRANSPOSE(OFFSET($V$1,TRANSPOSE(

IF(ISNUMBER(MATCH(MID(SUBSTITUTE(Sheet2!B2,",","") ,ROW(INDIRECT("1:"&LEN(SUB
STITUTE(Sheet2!B2,",","")))),2),Sheet2!$U$1:$U$50, 0)),

MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIREC T("1:"&LEN(SUBSTITUTE(Shee
t2!B2,",","")))),2),Sheet2!$U$1:$U$50,0),1))-1,0)))

Unfortunately, that has too many nested functions, so I had to
split
out


MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIREC T("1:"&LEN(SUBSTITUTE(Shee
t2!B2,",","")))),2),Sheet2!$U$1:$U$50,0)

and define it as a name (Ctrl-F3). I used a name of matches and a
Refersto
value of


=MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIRE CT("1:"&LEN(SUBSTITUTE(She
et2!B2,",","")))),2),Sheet2!$U$1:$U$50,0)

and then my formula is


=SUM(TRANSPOSE(OFFSET($V$1,TRANSPOSE(IF(ISNUMBER(m atches),matches,1))-1,0)))

which is an array formula, it should be committed with
Ctrl-Shift-Enter, not
just Enter.

I also had to enter a dummy value in row 1 of your lookup table
with a
weight of 0 to get it to work.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Terranoman" wrote in
message
...
I am attempting to set up a spreadsheet whereby I can summarise
the
weight
load of a vehicle. The cells will contain a variation of
abbreviated
texts
in the same cell e.g.

An example of column B contents is ah, zy, pd, (in a single cell
on
each
row)

Each abbreviation will have it's own unique number (weight)
allocated
in
two
seperate column which the formula will refer to - i.e

Column IU IV
Row 1 ah 2.2
Row 2 zy 3.7
Row 3 pd 1.4

There will be approximately 50 such text references and values.

Currently column b (rows 1 to 50) contain the varied text (ah, zy,
pd
etc
in
a single cell). Is it possible to 'find' the idividual text
abbreviation
(ah, zy or pd) in column B and total the allocated weight using
sumif
or
sumproduct even though they are in the same cell?

Any suggestions greatly appreciated.











  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,886
Default SUMIF or SUMPRODUCT to total cells containing multiple texts

not U and I

should have read
not U and V

--
Regards

Roger Govier


"Roger Govier" wrote in message
...
Hi Bob

Phhhewwww.....
That's great.
Just a very small point, the OP was using IU and IV for his columns
for the lookup table, not U and I and he did not say he was using
Sheet2. I used a second sheet in my suggestion.
As long as he makes those minor mods, he will have a superb solution.

I had totally missed that there were comma's separating the values in
the source cell in my rather mundane solution which would necessitate
a change to the formula to
=IF(Sheet1!A1="","",MID(Sheet1!$A1,1+(COLUMN(A1)-1)*4,2))

I think he should go with your solution.

--
Regards

Roger Govier


"Bob Phillips" wrote in message
...
I managed it with a formula that basically looks like this assuming
the data
is in B2

=SUM(TRANSPOSE(OFFSET($V$1,TRANSPOSE(
IF(ISNUMBER(MATCH(MID(SUBSTITUTE(Sheet2!B2,",","") ,ROW(INDIRECT("1:"&LEN(SUB
STITUTE(Sheet2!B2,",","")))),2),Sheet2!$U$1:$U$50, 0)),
MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIREC T("1:"&LEN(SUBSTITUTE(Shee
t2!B2,",","")))),2),Sheet2!$U$1:$U$50,0),1))-1,0)))

Unfortunately, that has too many nested functions, so I had to split
out

MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIREC T("1:"&LEN(SUBSTITUTE(Shee
t2!B2,",","")))),2),Sheet2!$U$1:$U$50,0)

and define it as a name (Ctrl-F3). I used a name of matches and a
Refersto
value of

=MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIRE CT("1:"&LEN(SUBSTITUTE(She
et2!B2,",","")))),2),Sheet2!$U$1:$U$50,0)

and then my formula is

=SUM(TRANSPOSE(OFFSET($V$1,TRANSPOSE(IF(ISNUMBER(m atches),matches,1))-1,0)))

which is an array formula, it should be committed with
Ctrl-Shift-Enter, not
just Enter.

I also had to enter a dummy value in row 1 of your lookup table with
a
weight of 0 to get it to work.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Terranoman" wrote in message
...
I am attempting to set up a spreadsheet whereby I can summarise the
weight
load of a vehicle. The cells will contain a variation of
abbreviated

texts
in the same cell e.g.

An example of column B contents is ah, zy, pd, (in a single cell on
each

row)

Each abbreviation will have it's own unique number (weight)
allocated in

two
seperate column which the formula will refer to - i.e

Column IU IV
Row 1 ah 2.2
Row 2 zy 3.7
Row 3 pd 1.4

There will be approximately 50 such text references and values.

Currently column b (rows 1 to 50) contain the varied text (ah, zy,
pd etc

in
a single cell). Is it possible to 'find' the idividual text
abbreviation
(ah, zy or pd) in column B and total the allocated weight using
sumif or
sumproduct even though they are in the same cell?

Any suggestions greatly appreciated.







  #12   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11
Default SUMIF or SUMPRODUCT to total cells containing multiple texts

Unfortunately I've inherited the spreadsheet and attempting to make it work
(without the use of a calculator and pen). Is it possible that I could
forward it on for perusal? You guys are way beyond my excel knowledge.

"Roger Govier" wrote:

not U and I


should have read
not U and V

--
Regards

Roger Govier


"Roger Govier" wrote in message
...
Hi Bob

Phhhewwww.....
That's great.
Just a very small point, the OP was using IU and IV for his columns
for the lookup table, not U and I and he did not say he was using
Sheet2. I used a second sheet in my suggestion.
As long as he makes those minor mods, he will have a superb solution.

I had totally missed that there were comma's separating the values in
the source cell in my rather mundane solution which would necessitate
a change to the formula to
=IF(Sheet1!A1="","",MID(Sheet1!$A1,1+(COLUMN(A1)-1)*4,2))

I think he should go with your solution.

--
Regards

Roger Govier


"Bob Phillips" wrote in message
...
I managed it with a formula that basically looks like this assuming
the data
is in B2

=SUM(TRANSPOSE(OFFSET($V$1,TRANSPOSE(
IF(ISNUMBER(MATCH(MID(SUBSTITUTE(Sheet2!B2,",","") ,ROW(INDIRECT("1:"&LEN(SUB
STITUTE(Sheet2!B2,",","")))),2),Sheet2!$U$1:$U$50, 0)),
MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIREC T("1:"&LEN(SUBSTITUTE(Shee
t2!B2,",","")))),2),Sheet2!$U$1:$U$50,0),1))-1,0)))

Unfortunately, that has too many nested functions, so I had to split
out

MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIREC T("1:"&LEN(SUBSTITUTE(Shee
t2!B2,",","")))),2),Sheet2!$U$1:$U$50,0)

and define it as a name (Ctrl-F3). I used a name of matches and a
Refersto
value of

=MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIRE CT("1:"&LEN(SUBSTITUTE(She
et2!B2,",","")))),2),Sheet2!$U$1:$U$50,0)

and then my formula is

=SUM(TRANSPOSE(OFFSET($V$1,TRANSPOSE(IF(ISNUMBER(m atches),matches,1))-1,0)))

which is an array formula, it should be committed with
Ctrl-Shift-Enter, not
just Enter.

I also had to enter a dummy value in row 1 of your lookup table with
a
weight of 0 to get it to work.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Terranoman" wrote in message
...
I am attempting to set up a spreadsheet whereby I can summarise the
weight
load of a vehicle. The cells will contain a variation of
abbreviated
texts
in the same cell e.g.

An example of column B contents is ah, zy, pd, (in a single cell on
each
row)

Each abbreviation will have it's own unique number (weight)
allocated in
two
seperate column which the formula will refer to - i.e

Column IU IV
Row 1 ah 2.2
Row 2 zy 3.7
Row 3 pd 1.4

There will be approximately 50 such text references and values.

Currently column b (rows 1 to 50) contain the varied text (ah, zy,
pd etc
in
a single cell). Is it possible to 'find' the idividual text
abbreviation
(ah, zy or pd) in column B and total the allocated weight using
sumif or
sumproduct even though they are in the same cell?

Any suggestions greatly appreciated.







  #13   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10,593
Default SUMIF or SUMPRODUCT to total cells containing multiple texts

Doesn't my solution work then?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Terranoman" wrote in message
...
Unfortunately I've inherited the spreadsheet and attempting to make it

work
(without the use of a calculator and pen). Is it possible that I could
forward it on for perusal? You guys are way beyond my excel knowledge.

"Roger Govier" wrote:

not U and I


should have read
not U and V

--
Regards

Roger Govier


"Roger Govier" wrote in message
...
Hi Bob

Phhhewwww.....
That's great.
Just a very small point, the OP was using IU and IV for his columns
for the lookup table, not U and I and he did not say he was using
Sheet2. I used a second sheet in my suggestion.
As long as he makes those minor mods, he will have a superb solution.

I had totally missed that there were comma's separating the values in
the source cell in my rather mundane solution which would necessitate
a change to the formula to
=IF(Sheet1!A1="","",MID(Sheet1!$A1,1+(COLUMN(A1)-1)*4,2))

I think he should go with your solution.

--
Regards

Roger Govier


"Bob Phillips" wrote in message
...
I managed it with a formula that basically looks like this assuming
the data
is in B2

=SUM(TRANSPOSE(OFFSET($V$1,TRANSPOSE(

IF(ISNUMBER(MATCH(MID(SUBSTITUTE(Sheet2!B2,",","") ,ROW(INDIRECT("1:"&LEN(SUB
STITUTE(Sheet2!B2,",","")))),2),Sheet2!$U$1:$U$50, 0)),

MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIREC T("1:"&LEN(SUBSTITUTE(Shee
t2!B2,",","")))),2),Sheet2!$U$1:$U$50,0),1))-1,0)))

Unfortunately, that has too many nested functions, so I had to split
out


MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIREC T("1:"&LEN(SUBSTITUTE(Shee
t2!B2,",","")))),2),Sheet2!$U$1:$U$50,0)

and define it as a name (Ctrl-F3). I used a name of matches and a
Refersto
value of


=MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIRE CT("1:"&LEN(SUBSTITUTE(She
et2!B2,",","")))),2),Sheet2!$U$1:$U$50,0)

and then my formula is


=SUM(TRANSPOSE(OFFSET($V$1,TRANSPOSE(IF(ISNUMBER(m atches),matches,1))-1,0)))

which is an array formula, it should be committed with
Ctrl-Shift-Enter, not
just Enter.

I also had to enter a dummy value in row 1 of your lookup table with
a
weight of 0 to get it to work.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Terranoman" wrote in message
...
I am attempting to set up a spreadsheet whereby I can summarise the
weight
load of a vehicle. The cells will contain a variation of
abbreviated
texts
in the same cell e.g.

An example of column B contents is ah, zy, pd, (in a single cell on
each
row)

Each abbreviation will have it's own unique number (weight)
allocated in
two
seperate column which the formula will refer to - i.e

Column IU IV
Row 1 ah 2.2
Row 2 zy 3.7
Row 3 pd 1.4

There will be approximately 50 such text references and values.

Currently column b (rows 1 to 50) contain the varied text (ah, zy,
pd etc
in
a single cell). Is it possible to 'find' the idividual text
abbreviation
(ah, zy or pd) in column B and total the allocated weight using
sumif or
sumproduct even though they are in the same cell?

Any suggestions greatly appreciated.









  #14   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,886
Default SUMIF or SUMPRODUCT to total cells containing multiple texts

Hi

You can send it to me to look at if you wish.
Remove NOSPAM from my email address to mail direct.
I am going out now, but will take a look later this evening.

--
Regards

Roger Govier


"Terranoman" wrote in message
...
Unfortunately I've inherited the spreadsheet and attempting to make it
work
(without the use of a calculator and pen). Is it possible that I
could
forward it on for perusal? You guys are way beyond my excel
knowledge.

"Roger Govier" wrote:

not U and I


should have read
not U and V

--
Regards

Roger Govier


"Roger Govier" wrote in message
...
Hi Bob

Phhhewwww.....
That's great.
Just a very small point, the OP was using IU and IV for his columns
for the lookup table, not U and I and he did not say he was using
Sheet2. I used a second sheet in my suggestion.
As long as he makes those minor mods, he will have a superb
solution.

I had totally missed that there were comma's separating the values
in
the source cell in my rather mundane solution which would
necessitate
a change to the formula to
=IF(Sheet1!A1="","",MID(Sheet1!$A1,1+(COLUMN(A1)-1)*4,2))

I think he should go with your solution.

--
Regards

Roger Govier


"Bob Phillips" wrote in message
...
I managed it with a formula that basically looks like this assuming
the data
is in B2

=SUM(TRANSPOSE(OFFSET($V$1,TRANSPOSE(
IF(ISNUMBER(MATCH(MID(SUBSTITUTE(Sheet2!B2,",","") ,ROW(INDIRECT("1:"&LEN(SUB
STITUTE(Sheet2!B2,",","")))),2),Sheet2!$U$1:$U$50, 0)),
MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIREC T("1:"&LEN(SUBSTITUTE(Shee
t2!B2,",","")))),2),Sheet2!$U$1:$U$50,0),1))-1,0)))

Unfortunately, that has too many nested functions, so I had to
split
out

MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIREC T("1:"&LEN(SUBSTITUTE(Shee
t2!B2,",","")))),2),Sheet2!$U$1:$U$50,0)

and define it as a name (Ctrl-F3). I used a name of matches and a
Refersto
value of

=MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIRE CT("1:"&LEN(SUBSTITUTE(She
et2!B2,",","")))),2),Sheet2!$U$1:$U$50,0)

and then my formula is

=SUM(TRANSPOSE(OFFSET($V$1,TRANSPOSE(IF(ISNUMBER(m atches),matches,1))-1,0)))

which is an array formula, it should be committed with
Ctrl-Shift-Enter, not
just Enter.

I also had to enter a dummy value in row 1 of your lookup table
with
a
weight of 0 to get it to work.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Terranoman" wrote in
message
...
I am attempting to set up a spreadsheet whereby I can summarise
the
weight
load of a vehicle. The cells will contain a variation of
abbreviated
texts
in the same cell e.g.

An example of column B contents is ah, zy, pd, (in a single cell
on
each
row)

Each abbreviation will have it's own unique number (weight)
allocated in
two
seperate column which the formula will refer to - i.e

Column IU IV
Row 1 ah 2.2
Row 2 zy 3.7
Row 3 pd 1.4

There will be approximately 50 such text references and values.

Currently column b (rows 1 to 50) contain the varied text (ah,
zy,
pd etc
in
a single cell). Is it possible to 'find' the idividual text
abbreviation
(ah, zy or pd) in column B and total the allocated weight using
sumif or
sumproduct even though they are in the same cell?

Any suggestions greatly appreciated.









  #15   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,886
Default SUMIF or SUMPRODUCT to total cells containing multiple texts

Hi

As I said earlier, you can mail the file to me if you wish.
remove NOSPAM from my address to mail direct i.e roger at technology4u
dot co dot uk
Do the obvious with the spaces and dots.

I will be pleased to take a look and post back.

--
Regards

Roger Govier


"Terranoman" wrote in message
...
Unfortunately I've inherited the spreadsheet and attempting to make it
work
(without the use of a calculator and pen). Is it possible that I
could
forward it on for perusal? You guys are way beyond my excel
knowledge.

"Roger Govier" wrote:

not U and I


should have read
not U and V

--
Regards

Roger Govier


"Roger Govier" wrote in message
...
Hi Bob

Phhhewwww.....
That's great.
Just a very small point, the OP was using IU and IV for his columns
for the lookup table, not U and I and he did not say he was using
Sheet2. I used a second sheet in my suggestion.
As long as he makes those minor mods, he will have a superb
solution.

I had totally missed that there were comma's separating the values
in
the source cell in my rather mundane solution which would
necessitate
a change to the formula to
=IF(Sheet1!A1="","",MID(Sheet1!$A1,1+(COLUMN(A1)-1)*4,2))

I think he should go with your solution.

--
Regards

Roger Govier


"Bob Phillips" wrote in message
...
I managed it with a formula that basically looks like this assuming
the data
is in B2

=SUM(TRANSPOSE(OFFSET($V$1,TRANSPOSE(
IF(ISNUMBER(MATCH(MID(SUBSTITUTE(Sheet2!B2,",","") ,ROW(INDIRECT("1:"&LEN(SUB
STITUTE(Sheet2!B2,",","")))),2),Sheet2!$U$1:$U$50, 0)),
MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIREC T("1:"&LEN(SUBSTITUTE(Shee
t2!B2,",","")))),2),Sheet2!$U$1:$U$50,0),1))-1,0)))

Unfortunately, that has too many nested functions, so I had to
split
out

MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIREC T("1:"&LEN(SUBSTITUTE(Shee
t2!B2,",","")))),2),Sheet2!$U$1:$U$50,0)

and define it as a name (Ctrl-F3). I used a name of matches and a
Refersto
value of

=MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIRE CT("1:"&LEN(SUBSTITUTE(She
et2!B2,",","")))),2),Sheet2!$U$1:$U$50,0)

and then my formula is

=SUM(TRANSPOSE(OFFSET($V$1,TRANSPOSE(IF(ISNUMBER(m atches),matches,1))-1,0)))

which is an array formula, it should be committed with
Ctrl-Shift-Enter, not
just Enter.

I also had to enter a dummy value in row 1 of your lookup table
with
a
weight of 0 to get it to work.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Terranoman" wrote in
message
...
I am attempting to set up a spreadsheet whereby I can summarise
the
weight
load of a vehicle. The cells will contain a variation of
abbreviated
texts
in the same cell e.g.

An example of column B contents is ah, zy, pd, (in a single cell
on
each
row)

Each abbreviation will have it's own unique number (weight)
allocated in
two
seperate column which the formula will refer to - i.e

Column IU IV
Row 1 ah 2.2
Row 2 zy 3.7
Row 3 pd 1.4

There will be approximately 50 such text references and values.

Currently column b (rows 1 to 50) contain the varied text (ah,
zy,
pd etc
in
a single cell). Is it possible to 'find' the idividual text
abbreviation
(ah, zy or pd) in column B and total the allocated weight using
sumif or
sumproduct even though they are in the same cell?

Any suggestions greatly appreciated.











  #16   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 208
Default SUMIF or SUMPRODUCT to total cells containing multiple texts

=SUMPRODUCT( ISNUMBER( FIND( $IU$1:$IU$50, B1 ) ) * $IV$1:$IV$50 )

Adjust to your ranges and Drag-fill as needed.
--
Festina Lente


"Terranoman" wrote:

I am attempting to set up a spreadsheet whereby I can summarise the weight
load of a vehicle. The cells will contain a variation of abbreviated texts
in the same cell e.g.

An example of column B contents is ah, zy, pd, (in a single cell on each row)

Each abbreviation will have it's own unique number (weight) allocated in two
seperate column which the formula will refer to - i.e

Column IU IV
Row 1 ah 2.2
Row 2 zy 3.7
Row 3 pd 1.4

There will be approximately 50 such text references and values.

Currently column b (rows 1 to 50) contain the varied text (ah, zy, pd etc in
a single cell). Is it possible to 'find' the idividual text abbreviation
(ah, zy or pd) in column B and total the allocated weight using sumif or
sumproduct even though they are in the same cell?

Any suggestions greatly appreciated.

  #17   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 208
Default SUMIF or SUMPRODUCT to total cells containing multiple texts

Use SEARCH instead of FIND if case sensitivity is a problem...
--
Festina Lente


"PapaDos" wrote:

=SUMPRODUCT( ISNUMBER( FIND( $IU$1:$IU$50, B1 ) ) * $IV$1:$IV$50 )

Adjust to your ranges and Drag-fill as needed.
--
Festina Lente


"Terranoman" wrote:

I am attempting to set up a spreadsheet whereby I can summarise the weight
load of a vehicle. The cells will contain a variation of abbreviated texts
in the same cell e.g.

An example of column B contents is ah, zy, pd, (in a single cell on each row)

Each abbreviation will have it's own unique number (weight) allocated in two
seperate column which the formula will refer to - i.e

Column IU IV
Row 1 ah 2.2
Row 2 zy 3.7
Row 3 pd 1.4

There will be approximately 50 such text references and values.

Currently column b (rows 1 to 50) contain the varied text (ah, zy, pd etc in
a single cell). Is it possible to 'find' the idividual text abbreviation
(ah, zy or pd) in column B and total the allocated weight using sumif or
sumproduct even though they are in the same cell?

Any suggestions greatly appreciated.

  #18   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 605
Default SUMIF or SUMPRODUCT to total cells containing multiple texts

Thank you for explaining. I remember the syntax for FIND incorrectly. I had the first and second argument reversed. After I have cleared that, it becomes clear to me that the formula should work. I am a fan of SUMPRODUCT, so I like what I see.

I wonder if a 50-row table is considered huge. Hope this is not too hard on the system. It will be nice if the poster provides some kind of feedback.

Epinn

"PapaDos" wrote in message ...
Use SEARCH instead of FIND if case sensitivity is a problem...
--
Festina Lente


"PapaDos" wrote:

=SUMPRODUCT( ISNUMBER( FIND( $IU$1:$IU$50, B1 ) ) * $IV$1:$IV$50 )

Adjust to your ranges and Drag-fill as needed.
--
Festina Lente


"Terranoman" wrote:

I am attempting to set up a spreadsheet whereby I can summarise the weight
load of a vehicle. The cells will contain a variation of abbreviated texts
in the same cell e.g.

An example of column B contents is ah, zy, pd, (in a single cell on each row)

Each abbreviation will have it's own unique number (weight) allocated in two
seperate column which the formula will refer to - i.e

Column IU IV
Row 1 ah 2.2
Row 2 zy 3.7
Row 3 pd 1.4

There will be approximately 50 such text references and values.

Currently column b (rows 1 to 50) contain the varied text (ah, zy, pd etc in
a single cell). Is it possible to 'find' the idividual text abbreviation
(ah, zy or pd) in column B and total the allocated weight using sumif or
sumproduct even though they are in the same cell?

Any suggestions greatly appreciated.


  #19   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 605
Default SUMIF or SUMPRODUCT to total cells containing multiple texts

An example of column B contents is ah, zy, pd, (in a single cell on each row)

=SUMPRODUCT( ISNUMBER( FIND( $IU$1:$IU$50, B1 ) ) * $IV$1:$IV$50 )


I am totally lost! Can't see that the formula works.

No file from Terranoman?

Epinn

"PapaDos" wrote in message ...
=SUMPRODUCT( ISNUMBER( FIND( $IU$1:$IU$50, B1 ) ) * $IV$1:$IV$50 )

Adjust to your ranges and Drag-fill as needed.
--
Festina Lente


"Terranoman" wrote:

I am attempting to set up a spreadsheet whereby I can summarise the weight
load of a vehicle. The cells will contain a variation of abbreviated texts
in the same cell e.g.

An example of column B contents is ah, zy, pd, (in a single cell on each row)

Each abbreviation will have it's own unique number (weight) allocated in two
seperate column which the formula will refer to - i.e

Column IU IV
Row 1 ah 2.2
Row 2 zy 3.7
Row 3 pd 1.4

There will be approximately 50 such text references and values.

Currently column b (rows 1 to 50) contain the varied text (ah, zy, pd etc in
a single cell). Is it possible to 'find' the idividual text abbreviation
(ah, zy or pd) in column B and total the allocated weight using sumif or
sumproduct even though they are in the same cell?

Any suggestions greatly appreciated.


  #20   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 208
Default SUMIF or SUMPRODUCT to total cells containing multiple texts

LOL, it does work.

I simply approach it the other way around.
Instead of looking for extracted parts of the string into a table, I look
for the table column in the string...
--
Festina Lente


"Epinn" wrote:

An example of column B contents is ah, zy, pd, (in a single cell on each row)


=SUMPRODUCT( ISNUMBER( FIND( $IU$1:$IU$50, B1 ) ) * $IV$1:$IV$50 )


I am totally lost! Can't see that the formula works.

No file from Terranoman?

Epinn

"PapaDos" wrote in message ...
=SUMPRODUCT( ISNUMBER( FIND( $IU$1:$IU$50, B1 ) ) * $IV$1:$IV$50 )

Adjust to your ranges and Drag-fill as needed.
--
Festina Lente


"Terranoman" wrote:

I am attempting to set up a spreadsheet whereby I can summarise the weight
load of a vehicle. The cells will contain a variation of abbreviated texts
in the same cell e.g.

An example of column B contents is ah, zy, pd, (in a single cell on each row)

Each abbreviation will have it's own unique number (weight) allocated in two
seperate column which the formula will refer to - i.e

Column IU IV
Row 1 ah 2.2
Row 2 zy 3.7
Row 3 pd 1.4

There will be approximately 50 such text references and values.

Currently column b (rows 1 to 50) contain the varied text (ah, zy, pd etc in
a single cell). Is it possible to 'find' the idividual text abbreviation
(ah, zy or pd) in column B and total the allocated weight using sumif or
sumproduct even though they are in the same cell?

Any suggestions greatly appreciated.





  #21   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,886
Default SUMIF or SUMPRODUCT to total cells containing multiple texts

Hi PapaDos

Very clever thinking. I like it!!

However, if there should be a repeat of items in cell B2, then it would
not get picked up for the second (or subsequent) occurrence. Bob's
formula does pick up multiple occurrences.

I have no idea whether the OP's data has single or multiple occurrences
of items. I still have not received any file from him / her.

--
Regards

Roger Govier


"PapaDos" wrote in message
...
LOL, it does work.

I simply approach it the other way around.
Instead of looking for extracted parts of the string into a table, I
look
for the table column in the string...
--
Festina Lente


"Epinn" wrote:

An example of column B contents is ah, zy, pd, (in a single cell
on each row)


=SUMPRODUCT( ISNUMBER( FIND( $IU$1:$IU$50, B1 ) ) * $IV$1:$IV$50 )


I am totally lost! Can't see that the formula works.

No file from Terranoman?

Epinn

"PapaDos" wrote in message
...
=SUMPRODUCT( ISNUMBER( FIND( $IU$1:$IU$50, B1 ) ) * $IV$1:$IV$50 )

Adjust to your ranges and Drag-fill as needed.
--
Festina Lente


"Terranoman" wrote:

I am attempting to set up a spreadsheet whereby I can summarise the
weight
load of a vehicle. The cells will contain a variation of
abbreviated texts
in the same cell e.g.

An example of column B contents is ah, zy, pd, (in a single cell
on each row)

Each abbreviation will have it's own unique number (weight)
allocated in two
seperate column which the formula will refer to - i.e

Column IU IV
Row 1 ah 2.2
Row 2 zy 3.7
Row 3 pd 1.4

There will be approximately 50 such text references and values.

Currently column b (rows 1 to 50) contain the varied text (ah, zy,
pd etc in
a single cell). Is it possible to 'find' the idividual text
abbreviation
(ah, zy or pd) in column B and total the allocated weight using
sumif or
sumproduct even though they are in the same cell?

Any suggestions greatly appreciated.





  #22   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 208
Default SUMIF or SUMPRODUCT to total cells containing multiple texts

If you need to get multiple hits, then this one should do it:
=SUMPRODUCT( ( LEN( B1 ) - LEN( SUBSTITUTE( LOWER( B1 ), LOWER( $IU$1:$IU$50
), "" ) ) ) / LEN( $IU$1:$IU$50 ), $IV$1:$IV$50 )
--
Festina Lente


"Roger Govier" wrote:

Hi PapaDos

Very clever thinking. I like it!!

However, if there should be a repeat of items in cell B2, then it would
not get picked up for the second (or subsequent) occurrence. Bob's
formula does pick up multiple occurrences.

I have no idea whether the OP's data has single or multiple occurrences
of items. I still have not received any file from him / her.

--
Regards

Roger Govier


"PapaDos" wrote in message
...
LOL, it does work.

I simply approach it the other way around.
Instead of looking for extracted parts of the string into a table, I
look
for the table column in the string...
--
Festina Lente


"Epinn" wrote:

An example of column B contents is ah, zy, pd, (in a single cell
on each row)

=SUMPRODUCT( ISNUMBER( FIND( $IU$1:$IU$50, B1 ) ) * $IV$1:$IV$50 )

I am totally lost! Can't see that the formula works.

No file from Terranoman?

Epinn

"PapaDos" wrote in message
...
=SUMPRODUCT( ISNUMBER( FIND( $IU$1:$IU$50, B1 ) ) * $IV$1:$IV$50 )

Adjust to your ranges and Drag-fill as needed.
--
Festina Lente


"Terranoman" wrote:

I am attempting to set up a spreadsheet whereby I can summarise the
weight
load of a vehicle. The cells will contain a variation of
abbreviated texts
in the same cell e.g.

An example of column B contents is ah, zy, pd, (in a single cell
on each row)

Each abbreviation will have it's own unique number (weight)
allocated in two
seperate column which the formula will refer to - i.e

Column IU IV
Row 1 ah 2.2
Row 2 zy 3.7
Row 3 pd 1.4

There will be approximately 50 such text references and values.

Currently column b (rows 1 to 50) contain the varied text (ah, zy,
pd etc in
a single cell). Is it possible to 'find' the idividual text
abbreviation
(ah, zy or pd) in column B and total the allocated weight using
sumif or
sumproduct even though they are in the same cell?

Any suggestions greatly appreciated.





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to use SUMIF to return sums between two values located in cells ScottBerger Excel Worksheet Functions 2 April 23rd 23 09:05 PM
Grand total time between two total cells Gwynn Excel Discussion (Misc queries) 4 May 9th 06 09:00 PM
SUMIF with multiple criteria macquarl Excel Discussion (Misc queries) 4 January 5th 06 02:18 AM
how can i ignore blank cells when multiple cells? arash Excel Worksheet Functions 4 November 17th 05 04:35 PM
using sumif & countif to sort multiple cells floridasurfn Excel Worksheet Functions 3 February 27th 05 09:23 PM


All times are GMT +1. The time now is 07:05 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"