Counting training records - countifs / Sum fuctions

[Arafruafrog]

Hi Forum I am try to set up a spreadsheet to count our team's training records.

On sheet 1 - I have the data in 4 columns as below

Date	Role	Staff Member Name	Training (mins)
21/09/2018	Admin	Staff 1	20
21/09/2018	Technical	Staff 2	120
01/10/2018	Admin	Staff 3	30

<tbody>
</tbody>

On Sheet 2 I have a table that I am summarizing the data.

Admin Technical
Sessions Time Sessions Time
Sept 1 ? 1 ?
Oct 1 ? 0 ?


What I am trying to do is sum the total of time (in minutes) for each role for a date period.

I am using this formula to get the count of sessions and it appears to be working.

=SUMPRODUCT(--(Training!$A$3:$A$100>=DATE(2018,10,1)),--(Training!$A$3:$A$100<=DATE(2018,10,31)),--(Training!$B$3:$B$100="Admin"))

I have tried this to get the minutes to add up for the date range for each role type but it is not working.

=COUNTIFS(Training!$A$3:$A$100,">="&DATE(2018,11,1),Training!$A$3:$A$100,"<="&DATE(2018,11,30),Training!$B$3:$B$100,"Admin")*(COUNT(Training!$D$24:$D$100))

Help would be greatly appreciated. Thanks Tee.

 

[AlanY]

you can modify your first formula to do it

=SUMPRODUCT(--(Training!$A$3:$A$100>=DATE(2018,10,1)),--(Training!$A$3:$A$100<=DATE(2018,10,31)),--(Training!$B$3:$B$100="Admin")[COLOR="#FF0000"],$D$3:$D$100[/COLOR])

btw, COUNTIFS() is the wrong function to sum things up and inconsistent range in Training!$D$24:$D$100 didn't help

 

[Arafruafrog]

many thanks that worked a charm i think it was a typo with the inconsistent range.