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

[TABLE="width: 531"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Role[/TD]
[TD]Staff Member Name[/TD]
[TD]Training (mins)[/TD]
[/TR]
[TR]
[TD]21/09/2018[/TD]
[TD]Admin[/TD]
[TD]Staff 1[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]21/09/2018[/TD]
[TD]Technical[/TD]
[TD]Staff 2[/TD]
[TD]120[/TD]
[/TR]
[TR]
[TD]01/10/2018[/TD]
[TD]Admin[/TD]
[TD]Staff 3[/TD]
[TD]30[/TD]
[/TR]
</tbody>[/TABLE]

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.