Count number of instances.

[jlokesh16]

I have three columns where the first column have names, second column has week (eg, Week 1, Week 2) and the third column has shifts (eg, S1, S2).
I want to count the number of instances of third column of a specific person of first column in a particular week.
For EG:

Col 1 Col 2 Col 3
Names Week Shifts.

Mike Week1 S1
Jack Week1 S1
Ray Week1 S2
Mike Week1 S1

How do i count how many times Mike has done S1 in Week1?

 

[Marcílio_Lobão]

lokesh16, Good evening.

Try to use:

=COUNTIFS(A1:A3,"MIKE",B1:B3,"WEEK1",C1:C3,"S1")

If.......D1 = MIKE......F1 = WEEK1.......G1 = S1 you can use:

=COUNTIFS(A1:A3,D1, B1:B3,F1, C1:C3,G1)

Is that what you want?
I hope it helps.

 

[sheetspread]

the pivot table does this too

 

[jlokesh16]

Thanks, The first two conditions are right, but as you mentioned S1, it is varied like S1, S2, S3. I want to count all the instances having "S" prefix in the column of that person in the week.

 

[sheetspread]

S1, it is varied like S1, S2, S3. I want to count all the instances having "S" prefix in the column of that person in the week.

Use sumproduct with a left(,1) then. If you want countifs or a pivot table instead, add a helper column.