Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Count to match three column text

0

I have an excel list as shown below – I wanted to count the occurrence matching THREE Columns:

 A                                              B             C

Practical

2/9/2019

Shift A

Practical

2/9/2019

Shift A

Practical

2/9/2019

Shift A

Practical

2/9/2019

Shift A

Practical

2/9/2019

Shift B

Practical

2/9/2019

Shift B

Practical

2/9/2019

Shift B

Theoretical

1/6/2019

Shift B

Theoretical

1/6/2019

Shift B

Theoretical

1/6/2019

Shift B

Theoretical

1/6/2019

Shift B

Theoretical

1/6/2019

Shift C

Theoretical

1/6/2019

Shift C

Theoretical

1/6/2019

Shift C

Theoretical

1/6/2019

Shift B

Theoretical

1/6/2019

Shift B

Theoretical

1/6/2019

Shift B

Practical

1/9/2019

Shift A

Practical

1/9/2019

Shift A

Practical

1/9/2019

Shift A

Practical

1/9/2019

Shift B

Practical

1/9/2019

Shift B

Need a formula to count How many "practical"  for  the particular "date"   "shift A" .

Answer should be è       On 2/9/2019  Shift A , Practical  =  4

                                               On 2/9/2019 Shift B, Practical  = 3

                                               On 1/9/2019 Shift A, Practical = 3 and so on

Answer
Discuss

Answers

0
Selected Answer

I copied your sample data to the range A32:C53. Then I entered the formula below in D32 and copied down.

=COUNTIFS($A$32:$A$53,$A32, $B$32:$B$53,$B32, $C$32:$C$53,$C32)

You can then create a list of unique values by ...

  1. copy column D to the clipboard and Paste Special > Values
    (to replace the formulas with their results).
  2. select Remove Duplicates from the Data tab, defining duplicates by all columns in range A32:D53
Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login