Want to count number of student grades in each group and subject

[rajys]

Hi,

In sheet "Dashboard" D5:L8 I want to count number of grades students get in each class group which is in column C. Class group will not remain constant and will change based on the data validation filter in C2. (I have kept Maths and English as an example)

Grades and Class group details from where results have to be calculated are in Sheet "Results"
Indirect reference ranges are in sheet "Indirect"

I tried using indirect countif formula but getting #Ref.

For example: Teaching group 11y/En1 has 17 students getting grade C (C3, C6 & C9)

Note: Grade A*= A*3, A*6 & A*9
Grade A = A3, A6 & A9, similar for other grades.

Could someone please let me know the formula that I can use to get the results.

Cheers,
Raj

[zbor]

Maybe this in D5 and pull accros table:

=SUMPRODUCT(--(LEFT(Results!$A$3:$A$241,LEN(D$4))=D$4),--(Results!$B$3:$B$241=$C5))

[rajys]

Great Thank you for the quick turnaround Zbor. This works fine for English subject. If I change the subject to Maths in Cell C2 this wont work as the formula looks for results and group only for english in Column A & B, where as the results and group for Maths are in Column D and E and likewise I will have around 30-35 shubjects.

I have slightly tweeked the formula and have added a if condition, but I don't think excel will take 30-35 if arguements.

Do you know a way of tweeking it slightly so that i need not use the if conditions for all 30-35 subjects.

[zbor]

I think you should reorganize your table and use Pivot table.
Then you'll be able to have all of that...

[rajys]

I reorganised my table and the formula works perfect.
Thank you so much

[Cutter]

An alternative would be to use COUNTIFS() with wildcard(s)

In D5 (and copied throughout): =COUNTIFS(Results!$B:$B,$C5,Results!$A:$A,D$4&REPT("?",LEN(D$4)))

[rajys]

Awesome Cutter, I tried that and it works perfect. Thank you so much.

[Cutter]

You're welcome. Don't forget to mark your thread as SOLVED (instructions are in rule #9 - click on Forum Rules at top of page to view).

[rajys]

Now that this is solved. but just out of curiosity I wanted to know if there is way to deal this using indirect countif formula as we do in Indirect vlookup.
To use the formulas suggested, I will have to reorder the table with subject grades for differnt subjects in the same column and same applies to the group information. Reordering the table is not a problem but just wanted to know if this was possible the other way also.

Some thing like below, but instead of vlookup i want to use countif formula.

=VLOOKUP($A6,INDIRECT(VLOOKUP($B$1,Term,1,FALSE)),17,FALSE)

[Cutter]

Upload your latest sample showing what you want to accomplish - use a couple of different examples of results you want.

[rajys]

Hi Cutter,

What I have currently done to use the formula advised by you and Zbor is, I have copied grades and Groups for English and Maths to the same columns J & K in the "results" sheet.

Instead, Will be possiible to count the number of A* (A*3, A*6 & A*9), A(A3,A6,A9) for English from column "A" from "Results" sheet when I choose English Language in "Dashboard!C2" and similarly for Maths from column "D" from the "Results" sheet when I choose Maths in "Dashboard!C2" using indirect countif function.
I choose subject and press "F9" to refresh, groups in Dashboard!C5:C8 will change by itself which is linked to hidden sheets classes.

As an example I have tried to create a indirect lookup references in "Indirect" Sheet. so my indirect formulas should refer to the "Indirect" sheet which is linked to "Results" sheet.

Please let me know if what I am trying to explain is not clear and as always thank you for your help.

Cheers,
Raj