Multi column counting 2

[Scott24x7]

Hi All,
I'm having a problem getting a count right. What I have are 3 columns of numbers:

Type1        Type2       Type3
67           95          33
62           72          73
77           119         220
210          15          55

1            2           1

So what I'm looking for is a in-cell function (and I tried this with COUNTIF and COUNTIFS but could get the syntax right I think), to at the bottom of each column it shows me how many times the column has the highest number in it. So assume the virst value 67 is in A2 then B2, C2, ect.
Is there some calculation I can put into A7, B7, C7 that will look across all three values in each row, and then determine which is higher and add that to the total?


Best Regards,
Scott
ATS, CDCE, CTIA, CTDC

"Everything should be made as simple as possible, and no simpler."

 

[JVFriederick]

Scott

Construct data table as follows :

Type1	Type2	Type3	MATCH
67	95	33	2
62	72	73	3
77	119	220	3
210	15	55	1
			
1	1	2

Formula in cell D2 =MATCH(MAX(A2:C2),A2:C2,0)
Formula in cell A7 =COUNTIF($D$2:$D$5,1)
Copy vertically and horizontally as needed.

Note : I think your example was incorrect, the max for each column was 1,1,2.

 

[Scott24x7]

Frederick, Oh, you are right. I miscounted the middle twice, it should have been 1,1,2.
I am trying your solution now.


Best Regards,
Scott
ATS, CDCE, CTIA, CTDC

"Everything should be made as simple as possible, and no simpler."

 

[Scott24x7]

Oh, there is no way to do this without creating the other column output? I had already figured out a way to do that, but I thought some combination of COUNTIFS() would allow it to all take place in a single cell on the column. The widths of the columns I'm working with are much greater, and I'm already out of "horizontal" space that I'm allocated for use on a screen. My example was made to be simplified. Any other solution?


Best Regards,
Scott
ATS, CDCE, CTIA, CTDC

"Everything should be made as simple as possible, and no simpler."

 

[JVFriederick]

Well, if horizontal space is an issue then simply hide the 'helper' column.

It could be on the left side of your data, new column A.

This is the simplest answer I could come up with, thinking it would be appealing......

 

[mintjulep]

I'd think an array function might be able to achieve this.

But they are barely understood magic to me.

 

[Scott24x7]

Wow, the Array Formula is really powerful... but I couldn't get it to work. I understand the results and why I got them, (I either got 1, 0 or 24, which made sense because the 1 was the "Highest" value in the array (P5:V58), or I got 0 because nothing matched, or I got 24 which was the total of populated cells in any one column...). So it was cool to play around with, but it never quite worked unfortunately.

I was able to hide the formula in an existing sell by making the result the same color as the fore and background, so it's there, but you can't "see" it in the worksheet. I would love to revisit this sometime though. I'm quite certain there is a solution there, but yes, they are very tricky to work with.

Best Regards,
Scott
ATS, CDCE, CTIA, CTDC

"Everything should be made as simple as possible, and no simpler."

 

[randy700]

I was able to hide the formula in an existing sell by making the result the same color as the fore and background

Or, you could adjust the column width to 0 (ZERO).


Randy

 

[bluedragon2]

Try: SUMPRODUCT((A1:A5>B1:B5)*(A1:A5>C1:C5)) and adjust for each colmun

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[Scott24x7]

Bluedragon,
My hat's off mate, that is brilliant, and does just what I want it to do in one cell... just brilliant. Wish I could give that 10 stars. I often forget about the power of SUMPRODUCT() and I haven't used it in about 5 years. Well done!

Best Regards,
Scott
ATS, CDCE, CTIA, CTDC

"Everything should be made as simple as possible, and no simpler."