Formulas with named ranges with 2 or more range areas

Hi All


I have a SUMIF() formula (reproduced below for immediate reference)

"=SUMIF(INDEX(Memberships_SBUSouth,,1),"Hosp
only",INDEX(Memberships_SBUSouth,,3))".

This works fine when the named range "Memberships_SBUSouth" contains
one range area e.g Sheet1!A1:C250. However, when the above named range
is expanded to include more than one (1) range areas (realized using
VBA through unionising multiple range areas) like so for example, if
the named range "Memberships_SBUSouth" refers to the range below :-


='[Combined National Monthly
Report.xlt]Memberships'!$B$601:$N$699,'[Combined National Monthly
Report.xlt]Memberships'!$B$106:$N$204


then the formula only gives the values corresponding to the "first" of
the multiple range areas ignoring all subsequent range areas.

Is there a way to get the result using named ranges consisting of
multiple range areas - these range areas may happen to be contiguous
but need not be so as in my example above.

Any assistance shall be highly valued.



Best regards



Deepak Agarwal


---
Message posted from http://www.ExcelForum.com/

HI Deepak,

I suggest you create an area which just duplicates the values in the source
areas into one contiguous area.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"agarwaldvk " wrote in message
...
Hi All


I have a SUMIF() formula (reproduced below for immediate reference)

"=SUMIF(INDEX(Memberships_SBUSouth,,1),"Hosp
only",INDEX(Memberships_SBUSouth,,3))".

This works fine when the named range "Memberships_SBUSouth" contains
one range area e.g Sheet1!A1:C250. However, when the above named range
is expanded to include more than one (1) range areas (realized using
VBA through unionising multiple range areas) like so for example, if
the named range "Memberships_SBUSouth" refers to the range below :-


='[Combined National Monthly
Report.xlt]Memberships'!$B$601:$N$699,'[Combined National Monthly
Report.xlt]Memberships'!$B$106:$N$204


then the formula only gives the values corresponding to the "first" of
the multiple range areas ignoring all subsequent range areas.

Is there a way to get the result using named ranges consisting of
multiple range areas - these range areas may happen to be contiguous
but need not be so as in my example above.

Any assistance shall be highly valued.



Best regards



Deepak Agarwal


---
Message posted from http://www.ExcelForum.com/