Sum column exclude cells which have maximum for row

[Hudco]

Almost New Year so best wishes for 2023.
I have a sheet with 12 columns and 250 rows of data which contain numbers in either 1,2,3,4 or 5 cells spread variously across the columns. I have created a 13th column with a formula counting the number of cells in each row with a number. A 14th column then extracts the maximum value in the rows where the count number in the 13th column is greater than 3.

I am wanting to sum the amounts in each column where the value in the 13th column is >3. I can do this successfully with a SUMIF formula [=SUMIF($M$4:$M$255,">3",A$4:A$255)]. But what I want to do is exclude the maximum value of any row of the 12 columns with more than 3 cells with numbers in that row. As stated above, I have extracted the maximum value of each row of the 12 columns however I cannot seem to create a formula to add the columns excluding the se values should they occur in the column being totalled.

The data are quarterly fees, or annual fees, or twice annual etc. and I want to get a value for each type of fee for each month.

Thanking you in anticipation

 

[KillerOfGiants]

Do you have some sample "scrubbed" data?

 

[Peter_SSs]

See if this (using smaller range) is what you want. I have added colour for my own checking. The column sums should exclude the orange cells because the row has less than 3 numbers and the yellow cells because they are the highest value in the rows with 3 or more numbers.

For the future I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

23 01 01.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
4	4	5		4	3	5	5	5	4	2	1	4	11	5
5	3	1	5	4	1	5	1	3	2	4	4	3	12	5
6	4				1								2
7	1	5	4		3				4	5			6	5
8													0
9	3	3		2	5				3	3	3	3	8	5
10	2	5		3	2	5	5	2	2	2	3		10	5
11								1					1
12	4							2	4	4	2	1	6	4
13	2	5	1	2	2	4		4		4	4	5	10	5
14	2	1	3	4	2	5	5	4	2	1	4	1	12	5
15	2	4	3	2	5	1	5	4	3	5	5	2	12	5
16	1	2	1	3		5	1	1	5		3	2	10	5
17	1						4	1	5		1	2	6	5
18	2	1	2		3	2	4	3	2	4	2	2	11	4
19	4											4	2
20	2	2	2	2	2	2	2	2	2	2	2	2	12	2
21
22	23	12	14	24	16	7	6	24	22	16	27	20
Sum with exclusions

Cell Formulas
Range	Formula
M4:M20	M4	=COUNT(A4:L4)
N4:N20	N4	=IF(M4>=3,MAX(A4:L4),"")
A22:L22	A22	=SUM(FILTER(A4:A20,($M4:$M20>2)*(A4:A20<>$N4:$N20)))

 

[Hudco]

Thanks Peter,

And thank you for the info about XL2BB, have downloaded and will install. I will also read up on Filter.

All the very best for the New Year.

Clyde

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.