Forum Discussion
How to use the COUNTIF/COUNTIFS function for Non-Linear Cells?
Ok, so the chart that I'm currently working on is a Behavioral Chart for my kids at the school I work at. The students range from 6-12th graders; the names of the student are in alphabetical order so rearranging in order for 6th graders to be grouped together isn't possible. But I'm trying to count their scores using the COUNTIF function for student who have scores greater than or equal to 5 and so on. I was able to do a similar formula using the COUNTIF function on Linear Cells but I want to count certain cells that are Non-Linear around the criteria I need. Below is the data I was able to generate just using the COUNTIF function:
| Equal to 5.00 | 3.00 | |
| Between 3.00 & 3.74 | 8.00 | |
| Greater Than 3.75 | 21.00 | |
| Greater Than 3.00 | 29.00 | |
| Less Than 3.75 | 46.00 | |
| Between 0.1 & 2.00 | 22.00 | |
I'm trying to find the right formula around the criteria below:
| Total | Equal to 5.00 | Between 3.00 & 3.74 | Greater Than 3.75 | Greater Than 3.00 | Less Than 3.75 | Between 0.1 & 2.00 | |||||||
| 6th Graders | 5 | ||||||||||||
| 7th Graders | 13 | ||||||||||||
| 8th Graders | 23 | ||||||||||||
| 9th Graders | 6 | ||||||||||||
| 10th Graders | 9 | ||||||||||||
| 11th Graders | 9 | ||||||||||||
| 12th Graders | 2 | ||||||||||||
Please, any insight is helpful.
Thanks for the detailed response. I believe the formulas you are looking for are contained in the example file I'm attaching. If this isn't correct. let me know and I can revisit the file and reassess them.
Please see attached and let me know if this works.
Lonnie-
Thanks for posting such a detailed description. I'm not quite sure I understand the dilemma. It appears you were able to get the results you anticipated in the first image. What is the difference between using the countif formulas in another range? Maybe I'm missing something simple?
A few things that would be helpful to aid in your solution are:
1. Example of what the raw data looks like (non-sensitive). A sample file would be best.
2. What are the actual formulas you are using?
3. A before and after mock up. What are you doing now? What is the expected end result?
Hello Matt,
Thanks for replying to my post. I've answered your questions below.
1. Example of what the raw data looks like (non-sensitive). A sample file would be best.
I've provide a sample document with non sensitive data.
2. What are the actual formulas you are using?
A few of the formulas I've tried are as follows: =COUNTIFS(E29,E30,E49,E50,E64,">=5.00"). This formula returned 0 as a result even after testing one of the cells with the prescribed criteria.
Equal to 5.00 Between 3.00 & 3.74 Greater Than 3.75 Greater Than 3.00 Less Than 3.75 Between 0.1 & 2.00 6th Graders 5 0 7th Graders 13 0 8th Graders 23 #REF! 9th Graders 6 #REF! 10th Graders 9 11th Graders 9 12th Graders 2 The other formulas I've tried are =SUM(INDIRECT(COUNTIFS(E68,E67,E65,E61,E60,E59,E58,E44,E41,E39,E38,E35,E34,E33,E31,E20,E19,E15,E13,E9,E6,E5,E4,">=5.00"))). Looking at the table above, this formula returned an error (#REF) for 8th Graders and the same for 9th Graders.
3. A before and after mock up. What are you doing now? What is the expected end result?
Essentially I'd like to use a formula and receive results for non-linear cells. When selecting cells I'm looking to select cells that are not directly after one another.
Initially there wasn't a detailed breakdown for certain criteria. As these students move through the semester and school year, we are looking to obviously monitor their behavior with certain averages on a weekly basis. We are looking to get a deeper look at how students, at certain grade levels (6th - 12th), are performing behaviorally. Essentially after this data package is complete I'd like to present or have in place a detailed report of those that are performing within a certain criteria, i.e., 6th graders behavior score that are above a 3.00, greater than or equal to 5.00, or even below 2.00. This data will be discussed with our school principal and teaching staff.
I hope you're able to help. Let me know if you have any additional questions.
Thanks for the detailed response. I believe the formulas you are looking for are contained in the example file I'm attaching. If this isn't correct. let me know and I can revisit the file and reassess them.
Please see attached and let me know if this works.
