Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 10-17-2014, 08:04 AM
dmcmillion dmcmillion is offline Count how many have completed each Training Course within a Date Range Windows 7 32bit Count how many have completed each Training Course within a Date Range Office 2010 32bit
Novice
Count how many have completed each Training Course within a Date Range
 
Join Date: Oct 2014
Posts: 2
dmcmillion is on a distinguished road
Question Count how many have completed each Training Course within a Date Range

I have one spreadsheet containing all Compliance courses taught this year, and the dates on which the employees have completed them.

Column A = Course Title
Column B = Employee Name
Column C = Completion Date of Course

I have to report how many employees have completed each of the various Compliance courses YTD, and then how many have completed each course during just the 3rd Quarter.



Since the report was run as a YTD report, I used COUNTIF(Report!$A:$A,"Course Title"), just changing out the names of the various courses, to calculate the YTD numbers.

How do I nest in another function to count by course title and by date range? I need to know how many records there are for Course A completed between 7/1/2014 and 9/30/2014, then for Course B for the same date range, then Course C for the same date range, etc.

Thanks in advance for your time and assistance!
Reply With Quote
  #2  
Old 10-17-2014, 11:53 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Count how many have completed each Training Course within a Date Range Windows 7 64bit Count how many have completed each Training Course within a Date Range Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

Hi
perhaps post a sample sheet. A Pivot Table might do the job
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #3  
Old 10-17-2014, 12:01 PM
dmcmillion dmcmillion is offline Count how many have completed each Training Course within a Date Range Windows 7 32bit Count how many have completed each Training Course within a Date Range Office 2010 32bit
Novice
Count how many have completed each Training Course within a Date Range
 
Join Date: Oct 2014
Posts: 2
dmcmillion is on a distinguished road
Default

Thanks, it probably would work, and Pivot Tables are one area I admit almost complete ignorance on!

I kept digging for an answer after I posted, and was able to use COUNTIFS to get what I needed.

So to count the number of employees who took Course A in the 3rd Quarter (July 1-Sept 30), I ended up with the following formula:

=COUNTIFS(Report!A:A,"Course A",Report!C:C,">=7/1/2014",Report!C:C,"<=9/30/2014")

There most likely is an easier or cleaner way...and I really need to learn how to use Pivot Tables!
Reply With Quote
  #4  
Old 10-20-2014, 09:59 AM
gebobs gebobs is offline Count how many have completed each Training Course within a Date Range Windows 7 64bit Count how many have completed each Training Course within a Date Range Office 2010 64bit
Expert
 
Join Date: Mar 2014
Location: Atlanta
Posts: 837
gebobs has a spectacular aura aboutgebobs has a spectacular aura about
Default

Yes, a pivot table would be much easier and allows for automated grouping by various date bins including quarter. If you post a sample sheet, one of us can buff one up for you.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Count how many have completed each Training Course within a Date Range Restrict Date Range Calculations akatz85 Excel 4 03-27-2014 08:46 PM
Date range mysteriously adding 4 years akatz85 Excel 7 03-25-2014 12:36 PM
Count how many have completed each Training Course within a Date Range How to count even exact date 90 days redza Excel 1 09-17-2013 03:36 AM
Make table cell active add date count date MelHerndon Word VBA 3 01-21-2013 04:23 PM
Count how many have completed each Training Course within a Date Range Date Range Prompt MikeWooZ Excel 6 12-04-2011 02:10 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 03:47 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft