I have a sheet with a couple hundred rows. Each row has an 'owner' and a pass/fail field. I am making a second sheet that has statistics for the users. So for each user it will count the number of passes, fails and non-completes. My question is how do I write a function to do this? My guess is its something like countif(sheet1!a1:aN, AND(a[i][owner]=name, a[i][pass/fail]=pass)
. Obviously though this syntax doesnt work in excel, so I would like to figure out how to do it
-
i suppose a simple pivot would do– FireeMar 4, 2014 at 17:48
-
Its already a pivot table. The pivot table works fine, but you have to manually set up the filters and then check the values. It would be nice if I could have a separate area with all the values good to go.– David says Reinstate MonicaMar 4, 2014 at 17:57
Add a comment
|
2 Answers
I figured out how to do it. Im still working out the kinks, but the basic idea is to use COUNTIFS
. The general format is something like COUNTIFS(NameRange,name,Pass/failRange,"pass")
.
If you had columns such as Student ID= Column A, and grade % = column J then you could write the formula like this: =COUNTIFS(A:A,A:A,J:J,"<60") This would give you a count of all courses for that particular student ID that is below 60%.