Most Occuring Item based off other cell equal cirteria

[keiserj]

Need help on this formula. Need cell I21 to find the most occuring issue if "'1st QRT Weld'!A2:50" equals "F21". Cell that the issues are in are on "1st QRT Weld'!F2:F50"

This is the formula that i have to find the most occuring in cell "1st QRT Weld'!E2:50"

=INDEX('1st QTR Weld'!F2:F50,MATCH(MAX(COUNTIF('1st QTR Weld'!F2:F50,'1st QTR Weld'!F2:F50)),COUNTIF('1st QTR Weld'!F2:F50,'1st QTR Weld'!F2:F50),0))) (Array Formula)

But need it to only find the most occuring issue if '1st QRT Weld'!A2:50" equals "F21".

This is what i tryed but coming up "0"

=COUNTIF('1st QTR Weld'!C:C,'1st QTR DATA'!F21INDEX('1st QTR Weld'!E2:E50,MATCH(MAX(COUNTIF('1st QTR Weld'!E2:E50,'1st QTR Weld'!E2:E50)),COUNTIF('1st QTR Weld'!E2:E50,'1st QTR Weld'!E2:E50),0)))

Any suggenstions

Thank you

[oeldere]

untested

=max(countif('1st QRT Weld'!$A$2:$A$50,$F$21)

[keiserj]

That only gives me the number of times "F21" appears within '1st QRT Weld'!A2:50" Which is 2 times. Within that i would like to see what is the most occuring issue in '1st QTR Weld'!F2:F50

[oeldere]

I suggest you upload an excel file,wihou confidential information.

[keiserj]

Here is a example of the spreadsheet all data need is showing. If question just ask

Thank you

[oeldere]

I17 =

Please Login or Register  to view this content.

please reply.

[keiserj]

I might not have been clear on what I was looking for. The suggestion givin is already what i have under Total Defect in the example spread sheet. In the yellow cell i want, out of the two(2) defects what was the item that occured the most. These items are located on 1st QRT WEld tab in the " F" column.

[oeldere]

I can show the data with an pivot table.



But this not quite what you ask.

See the attached file. (it was the same file, I already aded).

I lost the new file. => maybe tomorrow

[newdoverman]

I have used helper columns to help with the calculations. I have labelled them Helper 1, Helper 2 and Helper.

This solution first of all finds the max count for each model/defect. Then this count is combined with the model and this is used on the 1st QTR DATA to extract the defect ID with the max count for each model.

[keiserj]

Your helpers work but i need to have everything popualted one the 1st QRT WEld come to me. The helpers wouldnt be able to be in that spreadsheet. Thank you

[newdoverman]

Here is a version that eliminates the helper columns from 1st QTR WELD:

[keiserj]

Newdoverman thank you for the help i never thought of doing it that way.

[newdoverman]

Thank you for the feedback. If the table produced on 1st QTR DATA is in the way, select the whole table and drag it as far to the right as you like.