Hi, I would really appreciate some help with this. I am a teacher and I am putting together a spreadsheet for data to be entered. I have a table underneath which contains lots of different countif formulae so for example If test 1 is in column C and kids are graded different levels I can return how many kids got each level. Thats fine if I am looking at the whole year group but I also have a filter and I would like the table to return different values depending on the filter I select eg. counts all data if I filter all or just counts data from say Class 8A1.
I am stuck!! Please can someone help!
Not sure exactly what you need, but check out the SUBTOTAL formula.
Try adding a "helper" column which will return a 1 if that row is visible, a zero otherwise, e.g. in Z2 copied down
=SUBTOTAL(3,A2)
[assumes that column A is populated througout]
Now if you want to count "y"s in column G for example you can use a SUMPRODUCT formula like this to count them only in the filtered rows
=SUMPRODUCT((Z2:Z100=1)*(G2:G100="y"))
edit: or as you have Excel 2007 you can use COUNTIFS i.e.
=COUNTIFS(Z2:Z100,1,G2:G100,"y")
Audere est facere
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks