Sumproduct (ISNUMBER(SEARCH - Multiple criteria

[Dtex20]

Hi guys,

I've stumbled into a problem I've got this formula:

=SUMPRODUCT(((*Sheetname*$I:$I=$A37)*(*Sheetname*$H:$H>=$A$93)*(*Sheetname*$H:$H<=$A$94)*(ISNUMBER(SEARCH(HD$2,*Sheetname*$N:$N))))

I'm creating a countIFs, but I've hit a wall in my knowledge.. the only problem i have with the formula is the ISNUMBER(SEARCH part, for simplicity purposes i'll use the word "hello" that is stored in cell "HD$2".

So there are 4 ways "hello" can appear in Range ("N:N"). I want to countIF the following three ways:

,hello
hello,
hello

The fourth way "hello" can appear is:

?hello

"?" is 1 random character that character is always a letter (i want to avoid counting these):

ghello
phello
thello

I want to try do this with cell references, in one formula. Is this possible?

Thanks,

 

[Marcelo Branco]

Could you provide an example. say ~10 rows along with expected result?

M.

 

[Dtex20]

Could you provide an example. say ~10 rows along with expected result?

M.

I'm not quite sure what you mean, but i think you mean this:

A1:A10:

A1: ,hello hello hello hello, - Return 4
A2: ghello ,hello hello - Return 2
A3: phello ghello thello - Return 0
A4: hello hello hello - Return 3
A5: ,hello ,hello hello, - Return 3
A6: hello, hello, phello - Return 2
etc..

Lets say on the other sheet you had something like this in N10:

,hello,ghello - you would return a count of 1.

What is in column N is a list of strings separated by a comma, but it can be broken down into a solution that if it only counts:

,hello
hello,
hello

Each row in column N will only have 1 of those variations, so for example SEARCH(","&ND4 - will return the correct (1) result for any row that says.

bla,bla,bla,hello,bla,bla

i hope that makes sense?

Thanks,

 

[Marcelo Branco]

Maybe something like this


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

hello hello hello hello,​

[/td][td]

4​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

ghello ,hello hello​

[/td][td]

2​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

phello ghello thello​

[/td][td]

0​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

hello hello hello​

[/td][td]

3​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

hello ,hello hello,​

[/td][td]

3​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

hello, hello, phello​

[/td][td]

2​

[/td][/tr]
[/table]

Formula in B1 copied down
=(LEN(","&SUBSTITUTE(SUBSTITUTE(A1,",","")," ",",,")&",")-LEN(SUBSTITUTE(","&SUBSTITUTE(SUBSTITUTE(A1,",","")," ",",,")&",",","&"hello"&",","")))/LEN(","&"hello"&",")

Hope this helps

M.

 

[bosco_yip]

Or…………

In B1, copied down :

=(LEN(SUBSTITUTE(SUBSTITUTE(" "&A1,",",)," ","@"))-LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(" "&A1,",",)," ","@"),"@hello",)))/LEN("@hello")

Regards
Bosco

 

[Dtex20]

Maybe something like this


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

hello hello hello hello,​

[/TD]
[TD]

4​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

ghello ,hello hello​

[/TD]
[TD]

2​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

phello ghello thello​

[/TD]
[TD]

0​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

hello hello hello​

[/TD]
[TD]

3​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

hello ,hello hello,​

[/TD]
[TD]

3​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

hello, hello, phello​

[/TD]
[TD]

2​

[/TD]
[/TR]
</tbody>[/TABLE]


Formula in B1 copied down
=(LEN(","&SUBSTITUTE(SUBSTITUTE(A1,",","")," ",",,")&",")-LEN(SUBSTITUTE(","&SUBSTITUTE(SUBSTITUTE(A1,",","")," ",",,")&",",","&"hello"&",","")))/LEN(","&"hello"&",")

Hope this helps

M.

Hi,

My apologies, i gave you an AWFUL example. Let me try explain better...

[COLOR=#574123]=SUMPRODUCT(((*Sheetname*$I:$I=$A37)*(*Sheetname*$H:$H>=$A$93)*(*Sheetname*$H:$H<=$A$94)*(ISNUMBER(SEARCH(HD$2,*Sheetname*$N:$N))))[/COLOR]

• On Another sheet in each row of column ("N:N") is a list of words separated by a comma.

• On my current sheet on Row 2 is the word i'm trying to count. Which is why i'm using (ISNUMBER(SEARCH(HD$2,*Sheetname*$N:$N). The Word in HD$2 is "Tool".

• The problem is the word "Tool" could appear in the middle of a word for example "GRTool" Which counts as 1 because it still says "Tool".

PL Tool,GRTool,Tool - would count as three, but really Tool appears once.
PL Tool,Tool,GRTool - would count as Three, but really Tool appears once.

The problem is when the word i'm searching for appears in the middle of a word.

I'm basically looking for a way that says in between the comma's separating the words, is there anything else beside the exact string in the cell I've referenced. If there is don't count it.

Sorry for wasting your time.

Thanks.

 

[Aladin Akyurek]

@ Dtex20

You say:

PL Tool,GRTool,Tool- would count as three, but really Tool appears once.

PL Tool,Tool,GRTool - would count as Three, but really Tool appears once.


Why not clearly say whether the desired count is 3 or 1 for each row of data?

 

[Dtex20]

@ Dtex20

You say:

PL Tool,GRTool,Tool- would count as three, but really Tool appears once.

PL Tool,Tool,GRTool - would count as Three, but really Tool appears once.


Why not clearly say whether the desired count is 3 or 1 for each row of data?

Hi,

Because it is completely dynamic, the row could contain 10 different strings. Or It could contain two "Tool,Tool" which would be two.

And i have to do this for about 10,000 Rows.

Thanks