SOLVED

counting number of grade failures per student

%3CLINGO-SUB%20id%3D%22lingo-sub-2783531%22%20slang%3D%22en-US%22%3Ecounting%20number%20of%20grade%20failures%20per%20student%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2783531%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20everyone%2C%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20to%20count%20the%20number%20of%20Fs%20per%20student.%20How%20would%20I%20do%20this%20and%20have%20it%20calculate%20a%20%22fail%20count%22%20for%20each%20individual%20student%3F%20I%20also%20have%20to%20show%20this%20info%20on%20a%20pivot%20table.%3C%2FP%3E%3CP%3EI%20have%20attached%20a%20small%20example%20datasheet.%3C%2FP%3E%3CP%3EI%20actually%20need%20to%20perform%20this%20function%20for%20several%20hundred%20students.%26nbsp%3B%3C%2FP%3E%3CP%3EI%20tried%20to%20use%20countif%20and%20subtotal%20feature%20but%20could%20not%20get%20that%20to%20work%20with%20the%20pivot%20table%20that%20I%20need%20to%20show%20this%20data%20on.%26nbsp%3B%20Any%20help%20is%20appreciated.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2783531%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2783581%22%20slang%3D%22en-US%22%3ERe%3A%20counting%20number%20of%20grade%20failures%20per%20student%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2783581%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1166048%22%20target%3D%22_blank%22%3E%40jklemm22%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESomething%20like%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22JulianoPetrukio_0-1632517358778.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F312785iF9276E7D516D2593%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22JulianoPetrukio_0-1632517358778.png%22%20alt%3D%22JulianoPetrukio_0-1632517358778.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2783585%22%20slang%3D%22en-US%22%3ERe%3A%20counting%20number%20of%20grade%20failures%20per%20student%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2783585%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1166048%22%20target%3D%22_blank%22%3E%40jklemm22%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESee%20the%20attached%20version%20-%20it%20contains%20a%20pivot%20table.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello everyone, 

I need to count the number of Fs per student. How would I do this and have it calculate a "fail count" for each individual student? I also have to show this info on a pivot table.

I have attached a small example datasheet.

I actually need to perform this function for several hundred students. 

I tried to use countif and subtotal feature but could not get that to work with the pivot table that I need to show this data on.  Any help is appreciated. 

7 Replies

@jklemm22 

Something like this?

 

JulianoPetrukio_0-1632517358778.png

 

@jklemm22 

See the attached version - it contains a pivot table.

@Hans Vogelaar yes, this works great for the pivot table part - but is there a way to formulate this in the column next to the letter grade?

@Juliano-Petrukio yes, this works great for the pivot table part - but is there a way to formulate this in the column next to the letter grade?

best response confirmed by jklemm22 (New Contributor)
Solution

For Sure

 

=COUNTIFS($A$2:$A$20,$A2;$F$2:$F$20,"F")

COUNTIFS(ColumnWithStudents,StudentName,ColumnWithTheLetterResult,"F")

 

JulianoPetrukio_0-1632525335828.png

 

 

@Juliano-Petrukio I tried to copy/paste this formula into the appropriate cell and I got this error...

jklemm22_0-1632525364663.png

 

Dont worry. I already updated the post including an attachment with the solution.