How Can this be done?

[agarwaldvk]

Hi Guys

I have a named range called "memberships" comprising of 101 rows (fro
and including row 50 through to row 150) and 12 columns (from colum
"B" through to column "M"). The first 2 columns, i.e. columns "B" an
columns "C" are used for search criteria. Other columns from column "D
through to column "M" contain numerical values for summing up.

Hence, the named range "memberships" would be equivaluent to the rang
covered by B50:M150

I am looking for a way to add up the values in all the columns fro
column "D" through to column "M" only for those row where the conditio
for column "B" and the condition for column "C" are both met.

In my earlier query where column to be summed up was limited to on
column, RagdyeR had suggest something along the lines as shown belo
(in fact the formula below is just a reproduction of part of hi
suggestion - this worked perfectly well in the earlier context)

=SUMPRODUCT((ISNUMBER(SEARCH("advantage",A1:A100)))*(B1:B100="NSW")*C1:C100)

The equivalent formula that I used was like so :-

=SUMPRODUCT(ISNUMBER(SEARCH("Advantage",INDEX(memberships,,1)))*(INDEX(memberships,,2)="Effectiv
Change")*INDEX(memberships,,match($a$1,Memberships!$A$5:$AD$5,0)))


Effectively, what I am looking to do in the formula above is to replac
the match part i.e "match($a$1,Memberships!$A$5:$AD$5,0)" with a
appropriate descriptor/string/subfunction using the defined named rag
"memberships" to make it represnt an range equivalent to the rang
D50:M150.

I hope this is clear enough. If not, please let me know and I shall tr
to make it clearer if I can!


Best regards and thanks in advance!



Deepak Agarwa

 

[hgrove]

agarwaldvk wrote...

I have a named range called "memberships" comprising of 101
rows (from and including row 50 through to row 150) and 12
columns (from column "B" through to column "M"). The first 2
columns, i.e. columns "B" and columns "C" are used for search
criteria. Other columns from column "D" through to column "M"
contain numerical values for summing up. ...
I am looking for a way to add up the values in all the columns
from column "D" through to column "M" only for those row
where the condition for column "B" and the condition for
column "C" are both met. ...
The equivalent formula that I used was like so :-

=SUMPRODUCT(
ISNUMBER(SEARCH("Advantage",INDEX(memberships,,1)))
*(INDEX(memberships,,2)>="Effective Change")
*INDEX(memberships,,
match($a$1,Memberships!$A$5:$AD$5,0))) ...
replace the match part i.e
"match($a$1,Memberships!$A$5:$AD$5,0)"
with an appropriate descriptor/string/subfunction using the
defined named rage "memberships" to make it represnt an
range equivalent to the range D50:M150.

I'd figure you want something like

=SUMPRODUCT(
ISNUMBER(SEARCH("Advantage",INDEX(memberships,0,1)))
*(INDEX(memberships,0,2)="Effective Change")
*OFFSET(memberships,0,2,,COLUMNS(memberships)-2))

 

[agarwaldvk]

Harlan

Educate me on this a bit mate!

In your recommended solution (reproduced below)

=SUMPRODUCT(ISNUMBER(SEARCH("Advantage",INDEX(memberships,0,1)))*(INDEX(memberships,0,2)="Effectiv
Change")*OFFSET(memberships,0,2,,COLUMNS(memberships)-2))

Does this portion "COLUMNS(memberships)-2" count and return the tota
number of columns less 2 in the named range "memberships?

AND does

OFFSET(memberships,0,2,,COLUMNS(memberships)-2)

return an array of all the rows and all the columns less 2 within th
named range "memberships"?

I shall try this shortly anyhow but I wanted to know what I would b
doing instead of just using the formulas.

Best regards


Deepa

 

[agarwaldvk]

Harlan

Educate me on this a bit mate!

In your recommended solution (reproduced below)

=SUMPRODUCT(ISNUMBER(SEARCH("Advantage",INDEX(memberships,0,1)))*(INDEX(memberships,0,2)="Effectiv
Change")*OFFSET(memberships,0,2,,COLUMNS(memberships)-2))

Does this portion "COLUMNS(memberships)-2" count and return the tota
number of columns less 2 in the named range "memberships?

AND does

OFFSET(memberships,0,2,,COLUMNS(memberships)-2)

return an array of all the rows and all the columns less 2 within th
named range "memberships"?

I shall try this shortly anyhow but I wanted to know what I would b
doing instead of just using the formulas.

Best regards


Deepa

 

[Harlan Grove]

Educate me on this a bit mate!

....

Educate yourself.

Does this portion "COLUMNS(memberships)-2" count and return the total
number of columns less 2 in the named range "memberships?

Select the cell containing the formula, press [F2] to edit the formula,
highlight this term, and press [F9]. Excel will evaluate the highlighted
portion of the formula (if it were a valid formula on its own) and replace
the highlighted text with its evaluated result.

AND does

OFFSET(memberships,0,2,,COLUMNS(memberships)-2)

return an array of all the rows and all the columns less 2 within the
named range "memberships"?

Online help for the OFFSET function would explain this.