Conditional formatting or excel formula to identify columns where sums diff is 1

Hello all

Hope all is well.

I wanted to get help with this issue:

In the included sample I have 2 tables on top of each other with the sum of each column at the bottom. I wanted to find a way say by using conditional formatting in the top table that will highlight or put a border around columns in which the sum of the is a difference of +1 or -1 when compared to the any of the columns in the bottom table and vice versa.

For the included sample there is a border around columns that meet this criteria and the differ of +1 or -1 when is shown below the 2nd table.

Thank you so much and let me know if further clarification or questions are needed.

Originally Posted by lar9149

when compared to the any of the columns in the bottom table

When compared to any of the columns? Or the sum in the same column?

vice versa

Do you mean also do highlighting on the bottom table when has a difference of ±1 from the top table?

when compared to the sum of any of columns on the bottom table

This is what I was trying to clarify and I think you just restated it instead of answering my question. Your file does not show results like what you are describing, because your highlighting in the file doesn't seem to have anything to do with these requirements. Also it appears you are comparing t6 in the top table to the sum of t7 in the bottom table, so it doesn't matter which column. Your row labelled "diff" shows the difference between a column in the upper table and the same column in the lower table, not any column. So it appears the "diff" row is a red herring.

I think that clears up your requirements but I always like to make sure before I put effort into it.

OK here is conditional formatting that:

Highlights a column in the top table if the sum of the values in that table is 1 more or 1 less than the sum of any column in the bottom table

Highlights a column in the bottom table if the sum of the values in that table is 1 more or 1 less than the sum of any column in the top table

This works. Thank you so much for your time and help. It is super appreciated. Will make as resolve