Formulas with named ranges with 2 or more range areas

[agarwaldvk]

Hi All


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

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

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


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


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

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

Any assistance shall be highly valued.



Best regards



Deepak Agarwa

 

[Niek Otten]

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