Help: Count Of Rows With Maximum Values

[heathen3017]

I got a gut feeling this is a very simple solution and I'm overlooking it, and that's what's frustrating.

Here's a simple setup example of what I am looking for:

A B C
1 2 3
3 1 1
4 2 2
2 1 4

What I am looking for is a count of the rows in which Column A which has the greatest value for the row. In the example above, the number would be 2 since rows 2 and 3 have the greatest values for those rows.

Thanks in advance.

 

[Yahya]

Try this

Excel Workbook
	A	B	C	D	E	F
1	1	2	3			2
2	3	1	1
3	4	2	2
4	2	1	4
Sheet7

Excel 2007

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

 

[heathen3017]

Unfortunately, that gives a #VALUE! error. The error message is not very helpful.

 

[T. Valko]

I got a gut feeling this is a very simple solution and I'm overlooking it, and that's what's frustrating.

Here's a simple setup example of what I am looking for:

A B C
1 2 3
3 1 1
4 2 2
2 1 4

What I am looking for is a count of the rows in which Column A which has the greatest value for the row. In the example above, the number would be 2 since rows 2 and 3 have the greatest values for those rows.

Thanks in advance.

Try this...

With your data in the range A2:C5.

=SUMPRODUCT(--(A2:A5=SUBTOTAL(4,OFFSET(A2:C5,ROW(A2:C5)-ROW(A2),,1,3))))

 

[markmzz]

Or try this:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;;">1</td><td style="text-align: center;;">2</td><td style="text-align: center;;">3</td><td style="text-align: center;;">Total</td><td style="text-align: center;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">3</td><td style="text-align: center;;">1</td><td style="text-align: center;;">1</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">4</td><td style="text-align: center;;">2</td><td style="text-align: center;;">2</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">2</td><td style="text-align: center;;">1</td><td style="text-align: center;;">4</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E1</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">(<font color="Red">A1:A4>B1:B4</font>)*(<font color="Red">A1:A4>C1:C4</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
Markmzz

 

[heathen3017]

Or try this:

Excel 2007<table rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6" cellpadding="2.5px"><colgroup><col style="background-color: #E0E0F0" width="25px"><col><col><col><col><col></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th>
</th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;;">1</td><td style="text-align: center;;">2</td><td style="text-align: center;;">3</td><td style="text-align: center;;">Total</td><td style="text-align: center;;">2</td></tr><tr><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">3</td><td style="text-align: center;;">1</td><td style="text-align: center;;">1</td><td style="text-align: center;;">
</td><td style="text-align: center;;">
</td></tr><tr><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">4</td><td style="text-align: center;;">2</td><td style="text-align: center;;">2</td><td style="text-align: center;;">
</td><td style="text-align: center;;">
</td></tr><tr><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">2</td><td style="text-align: center;;">1</td><td style="text-align: center;;">4</td><td style="text-align: center;;">
</td><td style="text-align: center;;">
</td></tr><tr><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">
</td><td style="text-align: center;;">
</td><td style="text-align: center;;">
</td><td style="text-align: center;;">
</td><td style="text-align: center;;">
</td></tr><tr><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td></tr></tbody></table>

Sheet1​

<table rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" cellpadding="2.5px" width="85%"><tbody><tr><td style="padding:6px">Worksheet Formulas<table rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6" cellpadding="2.5px" width="100%"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">E1</th><td style="text-align:left">=SUMPRODUCT((A1:A4>B1:B4)*(A1:A4>C1:C4))</td></tr></tbody></table></td></tr></tbody></table>

Markmzz

Thank you very much. Apparently it was simpler than I thought...sometimes the simple solutions are what eludes you. You should've seen most of what I tried before, and I think they collapsed upon themselves due to their own complexity.

Thanks once again.

 

[markmzz]

Thank you very much. Apparently it was simpler than I thought...sometimes the simple solutions are what eludes you. You should've seen most of what I tried before, and I think they collapsed upon themselves due to their own complexity.

Thanks once again.

Heathen3017,

You're welcome.

Note: do some tests with the formula and tell us if it work.

Markmzz

 

[T. Valko]

Thank you very much. Apparently it was simpler than I thought...sometimes the simple solutions are what eludes you. You should've seen most of what I tried before, and I think they collapsed upon themselves due to their own complexity.

Thanks once again.

Just an observation...

That formula is just fine if you only have a "few" columns of data involved. I don't think you'd want to use that method if you had "a lot" of columns of data.

 

[T. Valko]

Or try this:

Excel 2007<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0; TEXT-ALIGN: center"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH></TR></THEAD><TBODY><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">Total</TD><TD style="TEXT-ALIGN: center">2</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: center">****</TD><TD style="TEXT-ALIGN: center">****</TD><TD style="TEXT-ALIGN: center">****</TD><TD style="TEXT-ALIGN: center">****</TD><TD style="TEXT-ALIGN: center">****</TD></TR></TBODY></TABLE>

Sheet1​

<TABLE style="BORDER-RIGHT: black 2px solid; PADDING-RIGHT: 0.4em; BORDER-TOP: black 2px solid; PADDING-LEFT: 0.4em; PADDING-BOTTOM: 0.4em; BORDER-LEFT: black 2px solid; PADDING-TOP: 0.4em; BORDER-BOTTOM: black 2px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all width="85%"><TBODY><TR><TD style="PADDING-RIGHT: 6px; PADDING-LEFT: 6px; PADDING-BOTTOM: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center" cellPadding=2 rules=all width="100%"><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0"><TH width=10>Cell</TH><TH style="PADDING-LEFT: 5px; TEXT-ALIGN: left">Formula</TH></TR></THEAD><TBODY><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>E1</TH><TD style="TEXT-ALIGN: left">=SUMPRODUCT((A1:A4>B1:B4)*(A1:A4>C1:C4))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Markmzz

I think you need to change that to...

=SUMPRODUCT((A1:A4>=B1:B4)*(A1:A4>=C1:C4))

=SUMPRODUCT(--(A1:A4>=B1:B4),--(A1:A4>=C1:C4))

 

[Yahya]

Unfortunately, that gives a #VALUE! error. The error message is not very helpful.

did you press Ctrl+Shift+Enter