Log In
Register
Home
Active Member
April 27, 2019
Offline
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")
See Answer
VIP
Trusted Members
December 7, 2016
Offline
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.
Answers Post
Active Member
April 27, 2019
Offline
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!
VIP
Trusted Members
December 7, 2016
Offline
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.
Active Member
April 27, 2019
Offline
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!
VIP
Trusted Members
December 7, 2016
Offline
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.
Active Member
April 27, 2019
Offline
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!
VIP
Trusted Members
December 7, 2016
Offline
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.
Active Member
April 27, 2019
Offline
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
1 Guest(s)
