SUMIF or SUMPRODUCT to total cells containing multiple texts

[Bob Phillips]

Absolutely not. If it were me, I think I would open a new thread as a
discussion thread, referencing back to the thread that made you stop and
think. That way, the OPs thread is left to run its course (we have to be
careful not to intimidate/scare off posters from asking follow-ups), but
your questions get a very clear forum of their own which people can join or
ignore as is there wont.

But never shut up, others will also read the threads and learn from them. I
go the COLUM(A2:F4)^0 trick from this, and I am sure I will use that in
future.

--
HTH

Bob Phillips

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

<< Nobody minds your questions......

Not everyone is as kind and patient as most of you are. I have to be
cautious ......

<< ...... hijack ......

I know, Bob. This is a sensitive issue and it has always been a concern for
me. I try to apologize before anyone complains. I always seem to have more
questions than anyone around. If I think open group discussion, archive,
easy reference for future research etc. then I can justify asking my
questions or providing my comments under the original poster's thread.
Having said this, I do understand that the poster has "ownership" and
certain privilege and it is also natural that he/she feels "possessive" and
doesn't want others to ask many questions or "take over" ......

I usually hold off my questions until the poster has got the answers and
"left" the forum.

What is the right approach? When unsure, shut up?

Epinn

Epinn,

Nobody minds your questions, you would soon hear if we did <bg>.

I can't speak for the OPs though whose threads you hijack <ebg>

--
HTH

Bob Phillips

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

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

That was the first thought that came to my mind when I briefly read the
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

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.

 

[Guest]

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

 

[Epinn]

Before I forget, I must apologize to the original poster for side tracking. Yes, Bob, opening a new thread and referencing back to the original thread may be the middle ground and I did think about this. I'll start right now. Bob, please move over to "What is the right approach?" thread.

Epinn

Absolutely not. If it were me, I think I would open a new thread as a
discussion thread, referencing back to the thread that made you stop and
think. That way, the OPs thread is left to run its course (we have to be
careful not to intimidate/scare off posters from asking follow-ups), but
your questions get a very clear forum of their own which people can join or
ignore as is there wont.

But never shut up, others will also read the threads and learn from them. I
go the COLUM(A2:F4)^0 trick from this, and I am sure I will use that in
future.

--
HTH

Bob Phillips

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

<< Nobody minds your questions......

Not everyone is as kind and patient as most of you are. I have to be
cautious ......

<< ...... hijack ......

I know, Bob. This is a sensitive issue and it has always been a concern for
me. I try to apologize before anyone complains. I always seem to have more
questions than anyone around. If I think open group discussion, archive,
easy reference for future research etc. then I can justify asking my
questions or providing my comments under the original poster's thread.
Having said this, I do understand that the poster has "ownership" and
certain privilege and it is also natural that he/she feels "possessive" and
doesn't want others to ask many questions or "take over" ......

I usually hold off my questions until the poster has got the answers and
"left" the forum.

What is the right approach? When unsure, shut up?

Epinn

Epinn,

Nobody minds your questions, you would soon hear if we did <bg>.

I can't speak for the OPs though whose threads you hijack <ebg>

--
HTH

Bob Phillips

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

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

That was the first thought that came to my mind when I briefly read the
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

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.

 

[Epinn]

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

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

No file from Terranoman?

Epinn

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

Adjust to your ranges and Drag-fill as needed.

 

[Guest]

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

 

[Epinn]

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

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

 

[Roger Govier]

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.

 

[Guest]

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 )

 

[Roger Govier]

Hi

If there are blank values within range IU1:IU50 then I get #DIV/0
errors.

Array entering
{=SUMPRODUCT( ( LEN( B1 ) - LEN( SUBSTITUTE( LOWER( B1 )
, LOWER( $IU$1:$IU$50 ), "" ) ) ) /
MAX(LEN( $IU$1:$IU$50 ),1), $IV$1:$IV$50 )}

seems to give the correct result though.

 

[Guest]

Since we are dealing with fixed length strings (2), we don't need the LEN(
$IU$1:$IU$50 ) at all:

=SUMPRODUCT( ( LEN( B1 ) - LEN( SUBSTITUTE( LOWER( B1 ), LOWER( $IU$1:$IU$50
), "" ) ) ) / 2, $IV$1:$IV$50 )

I posted the other one to make it a bit more generic.
Lookup tables should be a named range with no empty rows, so the #DIV/0
error is bothering me a lot less than having to use an array formula...

In any case, this one should work OK in most situations:

=SUMPRODUCT( ( LEN( B1) - LEN( SUBSTITUTE( LOWER( B1), LOWER( $IU$1:$IU$50
), "" ) ) ) / ( LEN( $IU$1:$IU$50 ) + ( LEN( $IU$1:$IU$50 ) = 0 ) ),
$IV$1:$IV$50 )

 

[Roger Govier]

Very nice.
Both work admirably, and I agree far better to avoid array entries.

 

[Guest]

The formula is working brilliantly....thankyou everyone. I appreciate the
time you've all spent on this.