Countif (or maybe it's Sumproduct?) meets two criteria and between two dates

Hello All,

See attachment.

Attendance Records will be my running data that I will continue to populate. This will contain the department, date and event.

Overview is where I would like to see my totals.

In the chart, Overview, I would like the formula to match the department and the event description above in row 5. Then count the number of times that department and event occur between the dates in row 3 and 4.

Thank you in advance,

Try this in C6 and filled down.

=COUNTIFS('Attendance Records'!$B:$B,Overview!$B6,'Attendance Records'!$G:$G,Overview!C$5,'Attendance Records'!C:C,">="&C$3,'Attendance Records'!C:C,"<="&C$4)

The problem you're going to have is when you fill it to the right.
Your dates in row 3 and 4 are Merged Cells
Take C3:D3, those are merged and show 22-June
The problem is that only C3 actually contains the date, D3 is actually blank.
You'll have to manually adjust the formula to refer to the date in the left cell of each date range.

hi,

Check this file


Punnam

Originally Posted by Jonmo1

Try this in C6 and filled down.

=COUNTIFS('Attendance Records'!$B:$B,Overview!$B6,'Attendance Records'!$G:$G,Overview!C$5,'Attendance Records'!C:C,">="&C$3,'Attendance Records'!C:C,"<="&C$4)

The problem you're going to have is when you fill it to the right.
Your dates in row 3 and 4 are Merged Cells
Take C3:D3, those are merged and show 22-June
The problem is that only C3 actually contains the date, D3 is actually blank.
You'll have to manually adjust the formula to refer to the date in the left cell of each date range.

Perfect. That works as I don't mind the extra little manual entry.

Much appreciate it!

You're welcome.

Originally Posted by Punnam

hi,

Check this file


Punnam

Oh nice. You did the manual entry for me... It's the same formula but it was dragged over. Not sure how you did that. But this works well too!

Welcome

Punnam