Find the largest amount of instances in column if criteria is met

[sharky12345]

I am trying to get a formula to count the values in column I but only if the value in column D is equal to B2. I need the result to be the largest amount of instances of the value found.

In simple terms, and assuming the criteria is met, I need to identify the value that occurs most frequently in column I.

 

[AhoyNC]

Assuming there would only be one mode, try is formula to identify the value:
This is an array formula and must be entered with CTRL-SHIFT-ENTER.

Excel Workbook
	A	B	C	D	E	F	G	H	I
2		3		1					1
3				3		5			5
4				5					4
5				4					2
6				3					5
7				2					10
8				3					5
9				2					4
10				3					3
11				2					10
12				3					5
13				3					5
Sheet

 

[joeu2004]

I am trying to get a formula to count the values in column I but only if the value in column D is equal to B2. [....] assuming the criteria is met, I need to identify the value that occurs most frequently in column I.

Perhaps, the following array-entered formula (press ctrl+shift+Enter instead of just Enter):

=IFERROR(MODE(IF(D2:D1000=B2,I2:I1000)), "none")

If there might be multiple "modes" and you would like all of them, use MODE.MULT instead of MODE. But in that case, select a range of cells that can accommodate the most number of "modes".

 

[sharky12345]

Guys,

Thanks - I tried the first one suggested by AhoyNC and it works just fine, but thanks to both!

 

[Peter_SSs]

Just wondering what result you would expect from this set of data and why?

Book1
	B	C	D	E	F	G	H	I
1
2	3		1					1
3			3					7
4			5					4
5			3					6
6			3					5
7			2					10
8			3					8
9			2					4
10			3					7
11			2					10
12			3					8
13			3					5
Mode