CODEWITHSUNDEEP
exceldateexcel-formulacountifsumproduct
meletkis
meletkis

Reputation: 29

COUNTIF in a matrix using different date formats

I am working on an excel sample data (Sheet 1) that is related to flight bookings. The columns I am interested in are the booking and check-in date. I have created on a separate sheet (Sheet 2) a matrix with dates grouped by month and year (mmm-yy). y-axis corresponds to the booking date whereas x-axis corresponds to the check-in date.

Using this matrix I tried to count the number of bookings that each month generated for the following ones. The problem I have with COUNTIFS is that I can't integrate into the code the conversion of Sheet 1 dates from dd-mm-yy to mmm-yy in order to count the bookings in the matrix

Any ideas?

Do you believe it's better to use Python in order to generate this piece of information? The next step would be to break down the aforementioned matrix in weeks.

Thank you in advance

Best regards, A

Upvotes: 0

Views: 131

Answers (1)

JvdV
JvdV

Reputation: 75900

You can get the result through the following formula in C3 on Sheet2:

=SUMPRODUCT((MONTH(Sheet1!$A$2:$A$10)=MONTH($B3))*(YEAR(Sheet1!$A$2:$A$10)=YEAR($B3))*(MONTH(Sheet1!$B$2:$B$10)=MONTH(C$2))*(YEAR(Sheet1!$B$2:$B$10)=YEAR(C$2)))

Drag right and down.

Upvotes: 0

Related Questions