Hello,
I need a formula that will give me a count of nonblank cells in a filtered range, when cells in both columns are nonblank. I'll give an example below, and I've attached a worksheet in case what I hacked together below isn't legible. I am using =SUMPRODUCT((G2:G13>E2:E13)*(SUBTOTAL(103,OFFSET(D2,ROW(D2:D13)-MIN(ROW(G2:G13)),0)))) to count how many scores increase between Quiz 1 and Quiz 2. Basically, I need a formula that would tell me how many people were present for both Quiz 1 and Quiz 2.
BONUS QUESTION: In some rare cases, like Tegan, someone in the dataset was present for Quiz 2 and not Quiz 1. When that happens, it reads them as an increase. In the example below, the number of increased should really be 4, but Tegan is making it a 5. Can I modify the formula above to only compare columns E and G when both are nonblank?
Thanks!
Phase Quiz 1 (T1) Exam A Quiz 2 (T2)
Susan___________1____________3___________43_______________3
Ian_____________1____________4___________74_______________5
Vicki____________1____________3___________35 ______________
Barbara__________1___________3____________53______________ 3
Zoe_____________2___________2____________54_______________3
Jamie____________2___________5___________12_______________5
Polly_____________2___________4___________43_______________5
Tegan____________4___________ ___________55_______________4
Turlough__________5___________ 5___________ _______________
Leela ____________4___________2___________67_______________2
Sarah Jane________3___________3___________56______________
Harry_____________4___________2___________63______________4
# of scores increased from T1 to T2: 5
# of people who were present at both T1 and T2: __?__
[Must be able to filter by Phase]
Bookmarks