count how many times certain text appears in one column while meeting criteria from another cell

[missrutele]

Hello,

I have this data for example. In column A I have country names in column B key words:

[TABLE="width: 295"]
<tbody>[TR]
[TD]Country[/TD]
[TD]Key words[/TD]
[/TR]
[TR]
[TD]Lithuania[/TD]
[TD]tableware; cutlery; pan;pot[/TD]
[/TR]
[TR]
[TD]Germany[/TD]
[TD]bag; backpack; pan[/TD]
[/TR]
[TR]
[TD]Germany[/TD]
[TD]pan; pot; backpack[/TD]
[/TR]
[TR]
[TD]Estonia[/TD]
[TD]bag; backpack;[/TD]
[/TR]
[TR]
[TD]France[/TD]
[TD]cutlery[/TD]
[/TR]
[TR]
[TD]France[/TD]
[TD]pan; pot; backpack[/TD]
[/TR]
[TR]
[TD]Bulgaria[/TD]
[TD]cutlery[/TD]
[/TR]
[TR]
[TD]Lithuania[/TD]
[TD]tableware[/TD]
[/TR]
</tbody>[/TABLE]

I can count how many times appears "cutlery" with formula =SUMPRODUCT((LEN(Table1[[#All],[Key words]])-LEN(SUBSTITUTE(Table1[[#All],[Key words]],"tableware,"")))/LEN("tableware"))

But I need to count for example how many times appears "cutlery" if country is "Lithuania".

It is possible to do that?

Thank you for your help in advance.

 

[FormR]

Hi, you can try like this.

=SUMPRODUCT(--(Table1[[#All],[Country]]="Lithuania"),(LEN(Table1[[#All],[Key words]])-LEN(SUBSTITUTE(Table1[[#All],[Key words]],"tableware","")))/LEN("tableware"))

 

[missrutele]

Thank you!

It worked