|
Matching two cells
Hi, I'm trying to get Excel to match information in two different columns and add the number of occurances, but unsure of how to do it.
Below I have listed 4 columns of information; I need to match for instance the number of times that in the first cell "06:15" matches the last cell "GAZ" and add the number of times this occurs, in the example it occurs "2" Anyone that can help please? 06:15 14:45 08:30 GAZ 08:00 16:30 08:30 GEORGIA 09:00 17:30 08:30 CLAIRE 11:45 20:15 08:30 KERRY 13:45 22:15 08:30 HELEN 06:15 14:45 08:30 CLAIRE 10:00 18:30 08:30 KERRY 13:45 22:15 08:30 GAZ 17:00 22:00 05:00 PAULA 08:00 16:30 08:30 GEORGIA 08:00 16:30 08:30 HELEN 12:00 20:30 08:30 KERRY 13:45 22:15 08:30 CLAIRE 17:00 22:00 05:00 PAULA 06:15 14:45 08:30 GAZ 10:00 18:30 08:30 HELEN 13:45 22:15 08:30 CLAIRE |
Matching two cells
Hi
Try putting the first value you are looking for in G1, and the second value in H1 then =SUMPRODUCT(($A$1:$A$1000=G1)*($D$1:$D$1000=H1)) -- Regards Roger Govier "bLOBBY" wrote in message ... Hi, I'm trying to get Excel to match information in two different columns and add the number of occurances, but unsure of how to do it. Below I have listed 4 columns of information; I need to match for instance the number of times that in the first cell "06:15" matches the last cell "GAZ" and add the number of times this occurs, in the example it occurs "2" Anyone that can help please? 06:15 14:45 08:30 GAZ 08:00 16:30 08:30 GEORGIA 09:00 17:30 08:30 CLAIRE 11:45 20:15 08:30 KERRY 13:45 22:15 08:30 HELEN 06:15 14:45 08:30 CLAIRE 10:00 18:30 08:30 KERRY 13:45 22:15 08:30 GAZ 17:00 22:00 05:00 PAULA 08:00 16:30 08:30 GEORGIA 08:00 16:30 08:30 HELEN 12:00 20:30 08:30 KERRY 13:45 22:15 08:30 CLAIRE 17:00 22:00 05:00 PAULA 06:15 14:45 08:30 GAZ 10:00 18:30 08:30 HELEN 13:45 22:15 08:30 CLAIRE -- bLOBBY |
Matching two cells
=SUMPRODUCT(--(A1:A20=TIMEVALUE("6:15")),--(D1:D20="GAZ"))
Regards, Stefi bLOBBY ezt *rta: Hi, I'm trying to get Excel to match information in two different columns and add the number of occurances, but unsure of how to do it. Below I have listed 4 columns of information; I need to match for instance the number of times that in the first cell "06:15" matches the last cell "GAZ" and add the number of times this occurs, in the example it occurs "2" Anyone that can help please? 06:15 14:45 08:30 GAZ 08:00 16:30 08:30 GEORGIA 09:00 17:30 08:30 CLAIRE 11:45 20:15 08:30 KERRY 13:45 22:15 08:30 HELEN 06:15 14:45 08:30 CLAIRE 10:00 18:30 08:30 KERRY 13:45 22:15 08:30 GAZ 17:00 22:00 05:00 PAULA 08:00 16:30 08:30 GEORGIA 08:00 16:30 08:30 HELEN 12:00 20:30 08:30 KERRY 13:45 22:15 08:30 CLAIRE 17:00 22:00 05:00 PAULA 06:15 14:45 08:30 GAZ 10:00 18:30 08:30 HELEN 13:45 22:15 08:30 CLAIRE -- bLOBBY |
Matching two cells
=SUMPRODUCT(--(A1:A20=TIME(6,15,0)),--(D1:D20="GAZ"))
"bLOBBY" wrote: Hi, I'm trying to get Excel to match information in two different columns and add the number of occurances, but unsure of how to do it. Below I have listed 4 columns of information; I need to match for instance the number of times that in the first cell "06:15" matches the last cell "GAZ" and add the number of times this occurs, in the example it occurs "2" Anyone that can help please? 06:15 14:45 08:30 GAZ 08:00 16:30 08:30 GEORGIA 09:00 17:30 08:30 CLAIRE 11:45 20:15 08:30 KERRY 13:45 22:15 08:30 HELEN 06:15 14:45 08:30 CLAIRE 10:00 18:30 08:30 KERRY 13:45 22:15 08:30 GAZ 17:00 22:00 05:00 PAULA 08:00 16:30 08:30 GEORGIA 08:00 16:30 08:30 HELEN 12:00 20:30 08:30 KERRY 13:45 22:15 08:30 CLAIRE 17:00 22:00 05:00 PAULA 06:15 14:45 08:30 GAZ 10:00 18:30 08:30 HELEN 13:45 22:15 08:30 CLAIRE -- bLOBBY |
| All times are GMT +1. The time now is 06:03 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com