0

I have a workbook that has a separate sheet that is assigned to each classroom teacher. In the column A of each sheet I have the student's names from their rosters listed, in column B I have it set up for them to place an "x" for each incomplete assignment for these particular students. Under my Roster sheet I want the workbook to search through each of the 30+ teacher sheets and find the names of each student and calculate the number of "x's" by their name throughout the sheets and report that info in my "Roster" sheet. Essentially counting how many incomplete assignments each student has throughout. This is the formula that I have on my Roster sheet:

=SUMPRODUCT(COUNTIFS(INDIRECT("'"&Sheetss&"'!A:A"),A2,INDIRECT("'"&Sheetss&"'!B:B"),"x"))

"Sheetss" is the range that I have defined for all of my teacher sheets.

My issue with this is that inside my first teacher sheet I can type a student's name (StudentB) in column A and an x in column B, it will return a 1 for StudentB on my Roster sheet, however, when typing StudentB on any other sheet and an x in column B on any other sheet it will not count that data and show a 2, 3, etc.

I feel as though the formula is not searching all of my sheets for data any help would be great.

  • indirect is not able to work with array. you will need to use all sheet names in VR {} array – user0 Mar 11 at 4:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.