2

I want to count dates monthwise with person name. This is my data:

Mandeep 1/2/2020
Mandeep 1/2/2020
Mandeep 1/2/2020
Mandeep 1/2/2020
Mandeep 1/2/2020
Mandeep 1/2/2020
Mandeep 2/2/2020
Mandeep 2/2/2020
Mandeep 2/2/2020
Mandeep 2/2/2020
Mandeep 2/2/2020
Nikhil  1/2/2020
Nikhil  1/2/2020
Nikhil  1/2/2020
Nikhil  1/2/2020
Nikhil  1/2/2020
Nikhil  1/2/2020
Nikhil  2/2/2020
Nikhil  2/2/2020
Nikhil  2/2/2020
Nikhil  2/2/2020
Nikhil  2/2/2020

The result should be

               Jan   Feb
Mandeep         6     5
nikhil          6     5

I need a formula for this.

1

You'll first need extra columns converting the dates to months and years. This is as simple as =MONTH(B2) and =YEAR(B2), applied to all rows.

Then you'll need a so-called pivot table to group the data. From the menu, choose Data → Pivot table; select the existing columns and where you want the table:

enter image description here

Add rows, columns and values as follows:

enter image description here

For month names, you can use the formula =TEXT(B2, "MMM") instead.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for?Browse other questions tagged or ask your own question.