Help Needed - Need to COUNTIFS then output max repeated value

**pjessop** · 07-18-2018, 08:03 PM

Hello, hoping someone can help me. I need an enhanced formula but I'm struggling.

I currently have this formula on a cell:

=COUNTIFS(Q31:Q301,"Joe Bloggs",H31:H301,"Fail")

This cell taking the range Q31:Q301 and looking for references of Joe Bloggs. When it finds Joe Bloggs it looks at the adjacent column (H31:H301) and on the adjacent row it sees if they passed or failed the test. The formula then outputs the number of fails attributed to Joe Bloggs in the given cell.

Each row on the spreadhseet is a CSV output of a person's pass or fail on a test.

However, in the next cell to the above formula (outputting the number of fails of Joe Bloggs), I need to expand this to look at column 'I'. Column 'I' range (I31:I301) which shows the type of subject the user failed on. I need to output to this next cell the type of test Joe Bloggs failed most on (eg Maths). The formula therefore looks for Joe Bloggs + Fails + outputs the subject that appears most linked to fails of Joe Bloggs.

An example of the structure would look like this:

Joe Bloggs | Pass | English
Ann Example | Fail | French
A.N.Other | Pass | English
Joe Bloggs | Fail | Maths
Ann Example | Fail | French
Ann Example | Fail | French
Joe Bloggs | Fail | Maths
A.N.Other |Fail | English
Joe Bloggs | Fail | Maths
Joe Bloggs | Fail | French
Joe Bloggs | Pass | Maths

In this simplified example, my formula at top would output '4' to my chosen cell (because there are 4 fails linked to Joe Bloggs), but I'm looking for the next cell to output the subject (from the range in column 'I') of which the user has failed most (in this example, this next cell would output "Maths")

Then, in the cell after that, (ok, I'm asking a lot now) to show the second most 'Fail' of a given user ('English' in the above example for Joe Bloggs).

Lastly, IF there is more than 1 value to output based on equal max count (eg Joe Bloggs had failed French twice and also Maths twice and those were the highest values), could the cell output both values 'Maths/French').

Also, if the user didn't fail any subjects and therefore the output of most failed subjects can't work, then for there not be an error or value!! message showing in the cell (just be empty if possible).

Thanks so much for any help anyone can provide.

PJ

**sandy666** · 07-18-2018, 08:12 PM

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

**FDibbins** · 07-18-2018, 11:25 PM

Hi, welcome to the forum

Have you considered just adding the extra criteria to your countifs (in the same cell or another 1), then using a filter to ID the top "fails"?

**pjessop** · 07-19-2018, 11:57 AM

Hi Sandy,

Many Thanks. Attached is my desensitized spreadsheet with reduced data. The areas I'm stuck on are in two places:

1 - The Orange Section from columns O, P, Q, R = Here I'm trying to output the error section that occurs most in column O, second most in P..etc that is relative to the user.

2 - The blank Yellow sections = Here I'd like to output the users who have had the most fails and in what section + most failed paper type

Also, I'd really like to work out how hide #DIV/0! outputs which occurs when there is a '0' cell that is factored into a formula. The formula is correct but there happens to be no data in the cell for it to work on.

Huge thanks

Paul