array sumif required, I think?

[RET79]

Two columns, like this

Name Data
RET79 534536
5345353234
98981
TJH 534530
424204240
242420


etc.

Now, I want to set something up so that if someone types RET79 in Cell D1 that Cell E1 will return the sum of the data for RET79.

Have played with sumif array forumlas but not manager to do it yet, can you help?

many thanks,

RET79

[Rocky E]

On 2002-05-17 14:37, RET79 wrote:
Two columns, like this

Name Data
RET79 534536
5345353234
98981
TJH 534530
424204240
242420


etc.

Now, I want to set something up so that if someone types RET79 in Cell D1 that Cell E1 will return the sum of the data for RET79.

Have played with sumif array forumlas but not manager to do it yet, can you help?

many thanks,

RET79

Sure if the names are in A:A and the data in B:B then D1= RET79 set E1=SUMIF(A:A,D1,B:B) No CSE required.

[RET79]

Rocky,

Many thanks for your solution, I have never seen one quite like that.

However, and this is probably my fault, but, this will only work given that the labels fill the cells in col A.

EG A2=RET79, A7 = ROCKY Data from B2 to B12.

So, if user types ROCKY in D1, then, E1 will return SUM(B7:B12) in effect.

With your soultion it only returns Sum(B7)

I'm sorry, I should have tried to get a HTML table so you could see it clearer.

RET79

[Nimrod]

Ret:
Can't you just drag & fill the names beside the data so that Rocky's great solution will work ?
Do it that way will give you more options in the future as well i.e. PVT's etc.

[RET79]

Nimrod,

Yes, I'm aware that doing that would work, however, due to various reasons the table structure has to be that way and filling in the labels will not be acceptable for the users of my spreadsheets.

I agree, it is a great solution and I have just realised I have been using many unecessary array formulas in the past.

RET79

[RET79]

I am not either a fan of using columns to make intermediate calculations and then hiding the columns, I am seeking an elegant solution, have tried hard myself but not succeeded.

RET79

[RET79]

But if I don't get another solution then I think I will use an intermediate column with all the labels filled in then I can use Rocky's elegant solution.

RET79

[Nimrod]

Here's a solution if you willing to hide a column.

IF
colA = Names
ColB = Hidden Column
ColC = Numbers
D2 = where you will enter Name to sum
D1 = Place sum Forumla

--ColB ( the hidden column)
Assuming starting data in A2:
Place this formula in B2 and drag down column
=IF(ISBLANK(A2),B1,A2)
(then hide colmn)

D1 = =SUMIF(B:B,D2,C:C)

D2 = enter criteria i.e. persons name

note( hidden column can be anywhere on sheet)..change formula accordingly

[RET79]

Nimrod,

Great solution and I will tuck that hidden column away somewhere away from the calcs, great idea, thankyou.

RET79

[Nimrod]

Cool Dude:
May I suggest that you not only hide it but use format-- Protection to hide and protect the formulas... Anyway glad you happy ...cheers