• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Enrollment Data|General Excel Questions & Answers|Excel Forum|My Online Training Hub

You are here: Home / Enrollment Data|General Excel Questions & Answers|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumGeneral Excel Questions & Answe…Enrollment Data
sp_PrintTopic sp_TopicIcon
Enrollment Data
Avatar
Stephen Patrick

Active Member
Members
Level 0
Forum Posts: 5
Member Since:
April 27, 2019
sp_UserOfflineSmall Offline
1
April 27, 2019 - 12:53 am
sp_Permalink sp_Print

Hey guys,

I was tasked with trying to determine how to set up a COUNTIFS formula that would tabulate Current Deposits (Column AC on Data Sheet) by week for every student contacted by the University.  Ultimately, we are trying to review the number of Applications- Current (Column AA on Data Sheet), the number of Accepted Applications (Column AB on Data Sheet), from the number of Students who actually enrolled (paid a deposit) under Column AC on Data Sheet.  We are strictly looking at whether the student was a Freshman, Transfer, Readmit, or Advanced Freshman (Column P).

 

We would like to show a running total of these items (I had pitched the idea of a pivot table to show some of this but the administration did not feel that was the best way to show it and preferred something in a similar manner in order to feed to several more tabs).  I had to remove most of the data due to file size restrictions to be able to upload this file so unfortunately, I wasn't able to show you everything.

The problem that we are having is that the information pulls correctly for 2019 and 2018.  However, the data for 2017 does not seem to be pulling correctly (even when we use the same formula).  It has not made sense to us yet as we keep trying several different things.  I have wondered whether or not an Index/Match combination might be better suited to pulling this data, but unfortunately, I keep running up against a wall on this.  Please let me know if there is anything more that you need from me as I would be glad to help if I have not explained this properly. 

The correct formula for 2019 is

=COUNTIFS(Data!O$2:O$50249,2019,Data!AC$2:AC$50249,"<="&B75,Data!P$2:P$50249,"Freshman")+COUNTIFS(Data!O$2:O$50249,2019,Data!AC$2:AC$50249,"<="&B75,Data!P$2:P$50249,"transfer")+COUNTIFS(Data!O$2:O$50249,2019,Data!AC$2:AC$50249,"<="&B75,Data!P$2:P$50249,"readmit")+COUNTIFS(Data!O$2:O$50249,2019,Data!AC$2:AC$50249,"<="&B75,Data!P$2:P$50249,"Advanced Freshman")

sp_AnswersTopicSeeAnswer See Answer
Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 870
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
2
April 27, 2019 - 10:20 am
sp_Permalink sp_Print

Hello,

I have done some tests and noticed that in ENTRYYEAR column there were lots of trailing spaces in the data, so I did a clean. I also changed the formula so there is no count for partial match, after those editings I believe the numbers are now correct.

Please check the attached file.

I also found a page with good information on different uses with COUNTIF.

sp_AnswersTopicAnswer
Answers Post
Avatar
Stephen Patrick

Active Member
Members
Level 0
Forum Posts: 5
Member Since:
April 27, 2019
sp_UserOfflineSmall Offline
3
April 27, 2019 - 11:15 am
sp_Permalink sp_Print

Thank you for your help on that!  Do you know why cell M77 is still showing a 0 in that field?  That was one that was confusing us - you seemed to have been able to get the data in Column L.  Let me know if you can!

 

Thanks again!

Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 870
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
4
April 27, 2019 - 5:57 pm
sp_Permalink sp_Print sp_EditHistory

Hello,

My first check didn't include the coloured columns in Weekly sheet, only the control in range J92:AI98.

I have now modified your formulas in range A1:Q78. I have not made any visual control to see if the numbers are correct, as I rely on my previous control, but do a check and see if the numbers are correct.

The reason to why cell M77 was showing a 0 was because the formula checked for data in Data sheet in wrong columns.
If you check you will see that the formula in cell M77 is different than in cell M76. This is also why I prefer to use structured references instead, makes it a lot easier to see any errors.

Avatar
Stephen Patrick

Active Member
Members
Level 0
Forum Posts: 5
Member Since:
April 27, 2019
sp_UserOfflineSmall Offline
5
April 30, 2019 - 5:10 am
sp_Permalink sp_Print

Wow - you are amazing!  You have solved something for us in such a short period for something that has taken so long to work on!

My only question on the tables, is really regarding the maintenance of them (how they were setup).  For instance, if I go to the data sheet and add information to Row 38480 (last row was 38479), that information will not pull over into the formulas.  However, if I insert a row in between row 38479 and 38478, that data will show up in the table.  How was the table originally defined?  Was that manually setup by you or does it pull off of columns - am I making sense?  Please let me know if you can and thanks again for all of your help!

Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 870
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
6
April 30, 2019 - 3:46 pm
sp_Permalink sp_Print sp_EditHistory

Hello,

i have tested the same, created two new rows in Data sheet, rows 38480 and 38481, entered data in columns N, O, P, AA and for the second new row also in column AB. In Weekly sheet the numbers for Applied and Accepted for Fall 2019 rose with 2 and 1 respectively, so it seems to work, at least I can't replicate your error.

Anteckning-2019-04-30-074430.pngImage Enlarger

Anteckning-2019-04-30-074801.pngImage Enlarger

sp_PlupAttachments Attachments
  • sp_PlupImage Anteckning-2019-04-30-074430.png (41 KB)
  • sp_PlupImage Anteckning-2019-04-30-074801.png (9 KB)
Avatar
Stephen Patrick

Active Member
Members
Level 0
Forum Posts: 5
Member Since:
April 27, 2019
sp_UserOfflineSmall Offline
7
May 4, 2019 - 6:07 am
sp_Permalink sp_Print

I really appreciate your help on all of this (more than you know!).  However, I am still running into a problem.

 

The file that I am attaching was the original file.  I essentially took all of your formulas, recreated them, and put them into the old file with all of the data.  The years 2018 & 2019 worked perfectly just as they did in your spreadsheet.  However, 2017 & 2016 are not working for me right now.  I have looked over the formulas and they appear to match perfectly to the ones that you used in your original file; however, I am not getting the data.

Do you know what I am doing wrong on this or does Excel just not like me? 🙂  I definitely appreciate all of your help and any insight as to why this error is occurring.  Please let me know if you can as I greatly appreciate it!

Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 870
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
8
May 4, 2019 - 6:59 pm
sp_Permalink sp_Print

Hello Stephen,

Nothing wrong with the formulas, but the data needs to be cleaned.

I just trimmed the ENTRYYEAR and STUDENTTYPE data and after that the numbers came in to your report.

Avatar
Stephen Patrick

Active Member
Members
Level 0
Forum Posts: 5
Member Since:
April 27, 2019
sp_UserOfflineSmall Offline
9
May 5, 2019 - 7:28 am
sp_Permalink sp_Print

Man that worked!  Can't thank you enough for your help on this!  That was really confusing me on why that information was not pulling.  How were you able to identify the cells that needed to be trimmed?  That way I can look for that in the future also - thanks again for all of your help!

Stephen

Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 870
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
10
May 6, 2019 - 7:01 am
sp_Permalink sp_Print

Hello Stephen,

Great that you have it all working. I am just glad that I could give a helping hand.

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Lynnette Altomari, Jessica Stewart, Roy Lutke, Jeff Krueger
Guest(s) 11
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Sopi Yuniarti
sandra parker
LAFONSO HERNANDEZ
Hayden Hao
Angela chen
Sean Moore
John Chisholm
vexokeb sdfg
John Jack
Malcolm Toy
Forum Stats:
Groups: 3
Forums: 24
Topics: 6214
Posts: 27243

 

Member Stats:
Guest Posters: 49
Members: 31894
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.