# Sum column exclude cells which have maximum for row



## Hudco (Dec 31, 2022)

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 (Jan 1, 2023)

Do you have some sample "scrubbed" data?


----------



## Peter_SSs (Jan 1, 2023)

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.xlsmABCDEFGHIJKLMN44543555421411553154151324431256412 71543456580 933253333851025325522231051111 124244216413251224444510514213425542141125152432515435521251612135115321051714151265182123243242211419442 2022222222222212221222312142416762422162720Sum with exclusionsCell FormulasRangeFormulaM4:M20M4=COUNT(A4:L4)N4:N20N4=IF(M4>=3,MAX(A4:L4),"")A22:L22A22=SUM(FILTER(A4:A20,($M4:$M20>2)*(A4:A20<>$N4:$N20)))


----------



## Hudco (Jan 1, 2023)

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 (Jan 1, 2023)

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


----------

