Forum Discussion
COUNTIFS Range Not Working
Hi, I'm a teacher who is trying to get data on my student's proficiency without manually counting individual numbers for 100+ students in 7 different criteria. I also can't upload the file because it contains information about my students that I cannot reveal.
Just to give an idea, this is what I'm working with. I've made the columns super small because I can't get the first three columns to freeze without it freezing a whole window, so this is my solution to keep track of what my rubric is out of while I finish grading. I will make them normal again when I'm done. My formulas here are all working correctly, and I have no issues. The issue is in my Proficiency scale below.
This is the formula I currently have in the active box in the image above: =COUNTIFS(D40:CU40,"<50,D40:CU40,>-1"). I didn't want it to disclude the zeroes or 49s, so I set it to one higher according to what I learned from researched on how to use COUNTIFS. From the screenshot, you see that I should have at least 1 student in the 0-49% (although it should be much higher calculating in the students who never submitted their essay but not pictured in my screenshots). However, the formula is not working, so I'm not sure what I did incorrectly. Any help would be appreciated. I need this data for Friday.
you are missing some quotes. try this:
=COUNTIFS(D40:CU40,"<50",D40:CU40,">-1")also to freeze both horizontal and vertical select the top left cell that you want in the moving section (i.e. all rows above it should freeze and all columns to the left of it should freeze) then select freeze panes
on another note you might consider transposing your data. Think about what IF you want to use the built in table filters because they only filter rows so does it make sense to look at a column and set a filter to refine your view. Presently I believe your columns are students so I don't think that would make much sense to filter the rows across all students based on 1 student's grade, but if you flip the table and the columns are classes then you might want to filter the rows (i.e. students) to see which students are presently failing or haven't submitted a report yet or are doing really well... just my 2 cents.
3 Replies
Hi,
To include both 0 and the number 49 in your formula calculation, you would need to write this formula like this:
=COUNTIFS(D40:CU40, ">=0", D40:CU40, "<=49")Make sure you have correctly set the range of cells you want to calculate.
Hope this helps.
IlirU
you are missing some quotes. try this:
=COUNTIFS(D40:CU40,"<50",D40:CU40,">-1")also to freeze both horizontal and vertical select the top left cell that you want in the moving section (i.e. all rows above it should freeze and all columns to the left of it should freeze) then select freeze panes
on another note you might consider transposing your data. Think about what IF you want to use the built in table filters because they only filter rows so does it make sense to look at a column and set a filter to refine your view. Presently I believe your columns are students so I don't think that would make much sense to filter the rows across all students based on 1 student's grade, but if you flip the table and the columns are classes then you might want to filter the rows (i.e. students) to see which students are presently failing or haven't submitted a report yet or are doing really well... just my 2 cents.
Thank you so much! This fixed my problem.
