Comparing text in several columns to identify and count enties that match the cr

[Ben]

Can soeone help me? I want to evaluate text in several
columns to identify and count the enties that match.
Exampe:
Column A Column E Column F Column K
Row1 From Home To Work By Bus For Work
Row2 From Work To Home By Bike For rest
Row3 From Work To Store By Car For Food
Row4 From Home To Work By Car For Work
Row5 From Work To Home By Bike For rest

I need a formula that will count how many time I left home
for work, by car. Or, Left work to go home (TO home) by
Bus, car or bike, etc... Any Ideas?

 

[Max]

One way via SUMPRODUCT()

Using your example set-up mentioned
(in cols A, E, F, K, row1 downwards)

Put in L1:
=SUMPRODUCT(($A$1:$A$5=M1)*($E$1:$E$5=N1)*($F$1:$F$5=O1))

and put your criteria in cols M, N and O, for example:

in M1: From Home (Criteria for col A)
in N1: To Work (Criteria for col E)
in O1: By Car (Criteria for col F)

(and so on, with other criteria down cols M, N and O)

L1 will return the count desired for the criteria in M1, N1, and O1

Copy L1 down col L

---------------------------
Expand formula to include col K, if required, e.g.:

In L1:
=SUMPRODUCT(($A$1:$A$5=M1)*($E$1:$E$5=N1)*($F$1:$F$5=O1))*($K$1:$K$5=P1))

and with Criteria for col K in col P, e.g.:

in P1: For Food