Cell Reference

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

hydro1guy - 15 Dec 2005 21:34 GMT

I have identified two cells in a table using vlookup & hlookup. I want to
sum the cells referred to by the lookups.

Reply to this Message

Nick Hodge - 15 Dec 2005 21:40 GMT

Hydro1guy

Just add the two lookups together

=VLOOKUP(Your_Vlookup)+HLOOKUP(Your-Hlookup)

Signature

HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS

>I have identified two cells in a table using vlookup & hlookup. I want to
> sum the cells referred to by the lookups.

Reply to this Message

hydro1guy - 16 Dec 2005 13:49 GMT

THat works but I want to sum the range of cells between the two. I think I
may have to use address but cannot get it to work. How else can I identify
the actual cell address for my range?

> Hydro1guy
>
[quoted text clipped - 4 lines]
> >I have identified two cells in a table using vlookup & hlookup. I want to
> > sum the cells referred to by the lookups.

Reply to this Message

Peo Sjoblom - 16 Dec 2005 14:15 GMT

If you are summing a range your best bet is to use INDEX but you need to
indentify the 2 cells (no need for ADDRESS really) maybe using MATCH

Signature

Regards,

Peo Sjoblom

> THat works but I want to sum the range of cells between the two. I think I
> may have to use address but cannot get it to work. How else can I identify
[quoted text clipped - 8 lines]
> > >I have identified two cells in a table using vlookup & hlookup. I want to
> > > sum the cells referred to by the lookups.

Reply to this Message

hydro1guy - 16 Dec 2005 16:16 GMT

    1    2    3    4    5    6
01-Jun    31    32    33    34    35    36
02-Jun    7    8    9    10    11    12
03-Jun    13    14    15    16    17    18
04-Jun    19    20    21    22    23    24
05-Jun    25    26    27    28    29    30

this is a sample table. The top row is hour and the first colum is date. I
want to sum A range of cells determined by state date/hour and end date
hour.I can find the cells to start and finish the range by using V&H lookup.
but cannot get the formula to sum them to work.

=sum((INDEX(B2:H6,VLOOKUP(B12,B2:H6,(B13+1)),HLOOKUP(B18,B2:H6,(B17+1)))))

help would be greatly appreciated

> If you are summing a range your best bet is to use INDEX but you need to
> indentify the 2 cells (no need for ADDRESS really) maybe using MATCH
[quoted text clipped - 12 lines]
> to
> > > > sum the cells referred to by the lookups.

Reply to this Message

Peo Sjoblom - 16 Dec 2005 16:40 GMT

OK, using your example, assume start date 3-June, end date 5-Jun
start hour 2 and end hour 5, using your example that would sum to 258,
with starts dat in B12, end in B13, start time in B17 and end in B18

=SUM(INDEX(B1:H6,MATCH(B12,B1:B6,0),MATCH(B17,B1:H1,0)):INDEX(B1:H6,MATCH(B1
3,B1:B6,0),MATCH(B18,B1:H1,0)))

Signature

Regards,

Peo Sjoblom

> 1 2 3 4 5 6
> 01-Jun 31 32 33 34 35 36
[quoted text clipped - 28 lines]
> > to
> > > > > sum the cells referred to by the lookups.

Reply to this Message