I am looking to figure out how to have excel do a calculation for me that I do not have to do manually. In my data set, Column E, On-Task Behavior, is a dichotomous yes/no text. Column G, Classroom Structure, is a group of 4 different options. The options are small group, whole group, participation, and independent. I want to know when the class is in one of the four structures (i.e., whole group) what percent of the time the student is on-task. So, I only want excel to count the cells with yes is Column E that correspond the Column G whole group. I am not sure what the formula needs to look like. I can do it manually or create columns for every step in the equation, but I would like to save space and just have it as one equation. Please advise.
Hi Jacqueline,
Welcome to our forum!
You can use the COUNTIFS function to count the number of students that have "Yes" in column E AND match the classroom structure you want to count, divided by the count of all students in the group. e.g. for Whole Group:
=COUNTIFS(Table1[Classroom Structure],"Whole Group",Table1[On-taskB Behaviour],"Yes")/COUNTIF(Table1[Classroom Structure],"Whole Group")
Mynda
Okay, so I rearranged the spreadsheet to be more visually friendly. Is there a way to show you what I need help with? Maybe I am not explaining it properly. The equation =COUNTIFS('Sheet1'!D7:AF7,"Whole Group",'Sheet1'!D5:AF5,"Yes")/COUNTIF('Sheet1'!D7:AF7,"Whole Group") works if I only need On-Task beahvior during Whole Group, but I also need On-Task behavior with Work Progress during Whole Group.
I'm having a hard time figuring out how to tell it AND. Both On-Task and Work Progress must be indicated as "YES." =COUNTIFS('Sheet1'[Classroom Structure],"Whole Group",'Sheet1'[On-Task],"Yes" AND 'Sheet1'[Work Progress], "YES")/COUNTIF('Sheet1'!D7:AF7,"Whole Group").
Please advise
It sounds like your data is not in a tabular format. Are you able to upload a sample Excel file so we can help you further?
This is a link to my data set. I only have a few days of data in there since I can't figure out how to calculate everything electronically.
Thanks, but I can't open the link. I've requested access. Hopefully you've received a message to that effect and can approve it.
Mynda
okay, try this one. It should work now. I wrote comments in on the data sheet. I really really appreciate all the help I can get on this.
Hi Jacqueline,
Ah, I see it's Google Sheets, not Excel. We don't support Google Sheets questions, but since this is a copy of an Excel function, you can use this formula:
=COUNTIF('Elias B.'!D21:AF21,"yes")/counta('Elias B.'!D21:AF21)
i.e. count all text in cells D21:AF21
Mynda
