Counting training records - countifs / Sum fuctions

Arafruafrog

New Member
Joined
Aug 25, 2017
Messages
11
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

DateRoleStaff Member NameTraining (mins)
21/09/2018AdminStaff 120
21/09/2018TechnicalStaff 2120
01/10/2018AdminStaff 330

<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

Well-known Member
Joined
Oct 30, 2014
Messages
3,970
Office Version
365, 2019, 2016
Platform
Windows
you can modify your first formula to do it

Code:
=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
 
Last edited:

Forum statistics

Threads
1,082,585
Messages
5,366,466
Members
400,892
Latest member
lamarh755

Some videos you may like

This Week's Hot Topics

Top