With the above formula, I get the correct answer of "58" in BJ2, but in BJ3, I get the wrong answer of "88" as in that row, as the array formula is not excluding AV3, AY3 or BB3.

I need the following:
The formula to work in all rows in the BJ column, not just in the first row
The excluded columns to be changed from the original "AV, AY, BB" to "AB,AC,AD,BE,BF,BG" (for all rows)

I have tried both solutions offered again, and did not find that they worked for me. Thanks for your patience...hopefully the sample spreadsheet will illustrate the issue more clearly...

I saw your comment right after I posted my sample file. I tried the first formula in your post based on Rgonzo's formula, and while it worked for BJ2, when I copied and pasted the formula down the column it did not work for the subsequent rows. Since I might have errors in the cells, I then tried your last formula, and it worked perfectly, both in BJ2 and in the subsequent rows after I copied the formula down the BJ2 column. Thanks very much!!

BTW, I want to be fair on points distribution here. From my vantage point, the formula that worked for me was the last one supplied, but I'm not an expert and want to make sure I'm not overlooking the usability of the other solutions provided...

Thanks Andrea - the formula in your question won't work as intended, it will sum all the values in Y2:BI2, for the reasons I gave in my answer above.

The first formula I suggested fixes that problem but doesn't fix the dragging down issue, hence my other suggestions.

Objectively I'd say that's the best approach, because of its simplicity, so you should accept my answer!

However I think that the approaches suggested by both Rgonzo1971 and Steveo are also valid. Rgonzo's didn't work for you because the ranges were wrong, all references to Y2:AE2 should be Y2:BI2. Rgonzo also fixed the ADDRESS function problem by adding 4 as 3rd parameter.

Steve's suggestion didn't work because of the flaw in your initial formula - fix that and it works OK too

.......so I would suggest you also award points to both of those experts, the exact split is up to you (and feel free to ignore my advice if you wish!)

Thanks, Barry, for your explanation on the problem with my original formula and the impact of that on potential solutions, as well as the guidance on awarding points, and last but not least, your elegant solution! And thanks, Rgonzo, for your generous note and solution, and to Steveo for your solution too.

I'm very pleased...

Andrea

0

Featured Post

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

A little background as to how I came to I design this code:
Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…

As freelancing is becoming more and more common in the tech industry, certain obstacles are proving to be a challenge to those who are used to more traditional, structured employment. This article is meant to help identify such obstacles and offer a…

The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…