This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

I am using the following array formula in BJ2 using relative references:

=SUM(IF(ISERROR(Y2:BI2)+NOT(ISERROR(FIND((ADDRESS(ROW(Y2:BI2),COLUMN(Y2:BI2))),"AB2,AC2,AD2,BE2,BF2,BG2"))),"",Y2:BI2))

When I copy the formula down the rest of the column, the cell references within the quotation marks are not changing and I need them to:

"AB2,AC2,AD2,BE2,BF2,BG2"

Can the array formula be modified so that all the cell references in the formula are relative, allowing me to copy and paste the formula down the column with references updating as required?

Thanks,

Andrea

=SUM(IF(ISERROR(Y2:BI2)+NO

When I copy the formula down the rest of the column, the cell references within the quotation marks are not changing and I need them to:

"AB2,AC2,AD2,BE2,BF2,BG2"

Can the array formula be modified so that all the cell references in the formula are relative, allowing me to copy and paste the formula down the column with references updating as required?

Thanks,

Andrea

Do more with

EXPERT OFFICE^{®} is a registered trademark of EXPERTS EXCHANGE^{®}

if you only want to change the row

pls try

```
=SUM(IF(ISERROR(Y2:AE2)+NOT(ISERROR(FIND((ADDRESS(ROW(Y2:AE2),COLUMN(Y2:AE2),4)),SUBSTITUTE("AB2,AC2,AD2,BE2,BF2,BG2","2",ROW(Y2))))),"",Y2:AE2))
```

Regards
replacing

"AB2,AC2,AD2,BE2,BF2,BG2"

with addresses formula

"AB"&ROW()&",AC"&ROW()&",A

should handle copying down a column

the Sum function is using an If statement? It seems that an a 'false' IF statement in SUM statement will generate the Y2:BI2 address range which would include columns you don't want.

I must ask... if you manually copy / change a couple of rows using your text (manual) method does tallying give you results you expect?

I extracted only the portion of the formula that needed work for relative referencing as your question asked...

I feel both solutions offered should be giving your the relative addressing you are looking for...

I am wondering about formula?

Please confirm you have verified formula to be giving you what you expect - if manually entered?

as written your formula isn't excluding those cells because the ADDRESS function is returning addresses like $AB$2 so those don't match the exclusion list because of the $ signs.

To fix that you need to include a 4 as final argument in the

=SUM(IF(ISERROR(Y2:BI2)+NO

but assuming you have no errors in those cells you can get the same result with simple SUM functions like this:

that can be copied down so that the references change. If you might have errors in those cell ranges try this version

=SUMIF(Y2:BI2,"<9.99E+307"

regards, barry

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!)

regards, barry

## Premium Content

You need an Expert Office subscription to comment.Start Free Trial