Tom M

<div>
See attached with Summary by Pivot Table<br><br><a href="https://filedb.experts-exchange.com/incoming/2021/08_w35/1570656/test-file.xlsx" rel="ugc">test-file.xlsx</a>
</div>


test-file.xlsx

<div>
Slight tweak to the formula as it wasn't picking up the one call after 22:00<br><br>=COUNTIFS('reports (12)'!$A:$A,$A2,'reports (12)'!$D:$D,$B$1,'reports (12)'!$J:$J,"&gt;="&amp;C$1,'reports (12)'!$J:$J,"&lt;"&amp;C$1+TIME(1,0,0))<br><br>This now compares with "Time in C1" and "Time in C1 plus 1 hour" rather than the next column; S1 was blank so wasn't giving correct result.<br><br>Advantage of Pivot Table means you wouldn't need to check the range of times as the Pivot will give results for all time ranges.
</div>


<div>
Pivot Table can also do a Pivot Chart but it looks odd in this scenario. It gives a series per time slot and an x axis position per agent; rather than x axis position per time slot and series per agent.<br><br>If you want the chart option, but sensibly displayed, you'd have to swap the rows and columns in the Pivot Table; a row per time slot and a column per agent.<br><br>Also, what about using the date as a filter. Again, with Pivot the date can be added to the Filter in the same way as Activity Detail (Answered), this would then just show the results for a particular date.
</div>


<div>
<div class="content wysiwyg-content fr-view">
<div><div class="content wysiwyg-content fr-view"><p>I am hoping to count the number of calls listed as answered in the data tab column D, when the agent name (column a) matches on the summary tab and data tab and when the times on data tab column J fall between the timestamps in row 1 of the summary sheet.</p><p><br></p><p>Basically, for the 08:00:00 AM column, we would count the number calls lists as answered for Agent Dispatch, Dispatch on the Data sheet that was greater than 08:00:00 AM and less than 09:00:00 AM. &nbsp;This would continue across the sheet counting the number of calls each agent is answering in those time periods</p><p><a class="fr-file" href="https://filedb.experts-exchange.com/incoming/2021/08_w35/1570647/test-file.xlsx" rel="ugc">test file.xlsx</a></p></div></div>
</div>
</div>



I am hoping to count the number of calls listed as answered in the data tab column D, when the agent name (column a) matches on the summary tab and data tab and when the times on data tab column J fall between the timestamps in row 1 of the summary sheet.
Basically, for the 08:00:00 AM column, we would count the number calls lists as answered for Agent Dispatch, Dispatch on the Data sheet that was greater than 08:00:00 AM and less than 09:00:00 AM.  This would continue across the sheet counting the number of calls each agent is answering in those time periods
test file.xlsx

Count if statement

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

Microsoft Excel

Microsoft Office is an integrated suite of applications that includes Outlook, Word, Excel, Access, PowerPoint, Visio and InfoPath, along with a number of tools to assist in making the individual components work together. Coding within and between the projects is done in Visual Basic for Applications, known as VBA.