Counting within columns

Occasional Visitor
applebananapeach
apple  
appleorangegrapes
applebananaorange
applepeach 
apple  
applegrapesbanana
banana apple

 

For a chart like the above, what function would I use to count the occurrence of two fruits, for example I want to count how many times apple & banana are paired across the three columns.

Thank you, 

2 Replies

@panlaura 

=COUNT(SEARCH("apple",A1:A8&B1:B8&C1:C8)*(SEARCH("banana",A1:A8&B1:B8&C1:C8)))

 

Maybe with above formula. Enter formula as arrayformula with ctrl+shift+enter if you don't work with Office365 or 2021.

 

@panlaura 

As variant for such layout

image.png

it could be

=SUM( --(  MMULT( (E2=$A$1:$C$8) + (F2=$A$1:$C$8), {1;1;1} ) > 1) )