Forum Discussion
Counting within columns
| apple | banana | peach |
| apple | ||
| apple | orange | grapes |
| apple | banana | orange |
| apple | peach | |
| apple | ||
| apple | grapes | banana |
| 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
As variant for such layout
it could be
=SUM( --( MMULT( (E2=$A$1:$C$8) + (F2=$A$1:$C$8), {1;1;1} ) > 1) )=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.
