Summing HLookup Values

[Guest]

I have a chart of numbers pertaining to progress of a project by month. The
end of month dates are in a row across the top, the progress % are in
corresponding rows and columns below:

31-mar-06 30-apr-06 31-may-06 30-jun-06
5.5 3.4 1.2 2.6

I'm trying to do a SUM of HLOOKUP values to get a total progress for a given
month. For example, I need to see the total progress for end of May from end
of March: SUM(HLOOKUP(5.5) to HLOOKUP(1.2))=5.5+3.4+1.2 in a cell at the
end of the row. How can I do this? Do I need to use another function?
Thanx............TomCat

 

[Biff]

Hi!

One way:

You need 2 cells to hold the span that you're interested in.

Assume the 12 EOM dates are in the range A1:L1

N1 = header = From
O1 = header = To

N2 = Mar
O2 = May

=SUMIF(A1:L1,">="&DATEVALUE(N2&"
"&2006),A2:L2)-SUMIF(A1:L1,">"&DATEVALUE(O2&" "&2006)+32,A2:L2)

Format the formula cell as GENERAL

Biff

 

[Biff]

I forgot something!

Assume the 12 EOM dates are in the range A1:L1

AND the numbers are in the range A2:L2.

Biff

 

[Guest]

I also forgot to mention that this project spans over several years and has
133 rows below the header line, which contains the dates.

Dates 31-mar-06 30-apr-06 31-may-06 30-jun-06 out to end dec
09
Task 1 5.5 3.4 1.2 2.6
Task 2 2.1 5.5 4.3 2.5
etc....down

Any way to make this easier?

Thanks............TC

 

[Guest]

Sorry, Biff. Didn't work.

I forgot something!


AND the numbers are in the range A2:L2.

Biff

 

[Guest]

Biff,

This doesn't work. I've tried it every which way but loose and can't get it
to adapt.

My dates are in Row 47 from L47 to CQ47, including quarters. The data I'm
trying to look up and sum is located 7 rows below in Row 53. I need to sum
the numbers between L53 (31-Dec-04) and BO53 (31-Mar-08) and divide the total
by 2.

I will be entering the date range in CU46 (31-Dec-04) and CV56 (31-Mar-08).
This range is variable so the formula must read the start date in CU46 and
the end date in CV56.

I will be doing this function for all 133 rows below Row 47.

In short, look up the start date and the end date in Row 47. Go down
vertically 7 rows and find the numbers associated with the start and end date
respectively. Sum those numbers and divide by 2.

Thanks again......Tommy

 

[Guest]

Perhaps give this a try as well ..

With EOM real dates as col headers assumed in B1:AU1, data in row2 down

Let's assume EOM date inputs for the desired start-end period will be
specified within AW1:AX1 (say), for eg: AW contains: 31-Mar-06, AX1 contains:
31-May-06

Then placed in AV2
=IF(OR($AW$1="",$AX$1=""),"",SUM(OFFSET(A2,,MATCH($AW$1,$B$1:$AU$1,0),,MATCH($AX$1,$B$1:$AU$1,0)-MATCH($AW$1,$B$1:$AU$1,0)+1)))
Copy AV2 down as far as required

Col AV should return the desired results ..

 

[Guest]

Re my response in the other branch ..

Try this adaptation in say, CT53
=IF(OR($CU$46="",$CV$46=""),"",SUM(OFFSET(K53,,MATCH($CU$46,$L$47:$CQ$47,0),,MATCH($CV$46,$L$47:$CQ$47,0)-MATCH($CU$46,$L$47:$CQ$47,0)+1))/2)
Copy CT53 down

 

[Biff]

Try this:

=(SUMIF(L47:CQ47,">="&CU46,L53:CQ53)-SUMIF(L47:CQ47,">"&CV56,L53:CQ53))/2

Biff

 

[Guest]

Max and Biff, thanks for your help. Biff, I could never get yours to work.
Max, yours worked great except in a couple of lines where it's off by .53.
That will be my mission for the morning.

Max: I need an explanation of what you did. Can you walk me thru the
formula? I saw some double commas there? I'm not familiar with that syntax.
If you could be so kind as to step me thru it!

Thanks VERY MUCH, Y'ALL!...........TomCat!

 

[Guest]

Max and Biff, thanks for your help. Biff, I could never get yours to work.
Max, yours worked great except in a couple of lines where it's off by .53.
That will be my mission for the morning.

Maybe re-check the values within the lines.
If it works, it should work ok for all lines as we copy down from CT53

Max: I need an explanation of what you did. Can you walk me thru the
formula? I saw some double commas there? I'm not familiar with that syntax.
If you could be so kind as to step me thru it!

At the crux of it is the OFFSET expression within the SUM(OFFSET(...))
ie, in partially decomposed form, the expression:

OFFSET(K53,,
MATCH($CU$46,$L$47:$CQ$47,0),,
MATCH($CV$46,$L$47:$CQ$47,0)-MATCH($CU$46,$L$47:$CQ$47,0)+1)

From help, the syntax is: OFFSET(reference,rows,cols,height,width), so for
the above

reference point is K53
ie the row 53 in question

rows param is zero (the optional zero is omitted here, with the rows param
represented by the double commas next to K53, viz.: K53,,)

cols param is returned by the expression:
MATCH($CU$46,$L$47:$CQ$47,0)
viz the position of the matched EOM startdate (CU46)
within the EOM array $L$47:$CQ$47

height param is zero (the optional zero is omitted here, with the height
param represented by the double commas next to the cols param, ie:
MATCH($CU$46,$L$47:$CQ$47,0),,

width param is returned by the expression:
MATCH($CV$46,$L$47:$CQ$47,0)-MATCH($CU$46,$L$47:$CQ$47,0)+1
which is essentially the difference between the matched EOM enddate's (CV46)
position within $L$47:$CQ$47 minus the matched startdate's (CU46) position
within $L$47:$CQ$47 plus "1" (+1) as an arithmetic adjustment

So the expression OFFSET(K53,...) will evaluate and return the range/array
of values within L53:CQ53 starting with the col of the matched EOM startdate
and ending exactly where the matched EOM enddate col lies

And when the OFFSET(K53,...) is copied down, the reference increments
relatively to OFFSET(K54,...), OFFSET(K55,...), and so on, hence returning
correspondingly the required range of values in row 54, 55, and so on as we
copy down

---

 

[Biff]

Biff, I could never get yours to work.

Well, I can put together a sample file that demonstrates that it does work
or, if you'd like to, you can send me a copy of your file and I'll put the
formula in for you. If you're interested in doing either of these just let
me know how to contact you.

Biff

 

[Biff]

Max's formula will work but it's like killing an ant with a sledgehammer
when all you needed to do was flick it with your finger! <g>

But if it works for you, great, that's what counts!

Biff

 

[Guest]

Well, I can put together a sample file that demonstrates that it does work ..

C'mon, Biff <g>. Don't hold back. Throw a link to your working sample here
to benefit all readers within the newsgroup who may be interested.

---

 

[Guest]

Max's formula will work but it's like killing an ant with a sledgehammer
when all you needed to do was flick it with your finger! <g>
But if it works for you, great, that's what counts

Thanks for that! It's my style to always strive to deliver more than what is
asked in the OP <bg> This may include: Front error traps,
absolute/mixed/relative cell references, warts and all, everything that's
needed to get going on the OP's end. The resulting concoction is of course
just one route to get to Rome, and it could almost always be refined further.


---

 

[Biff]

C'mon, Biff <g>. Don't hold back. Throw a link to your working sample here
to benefit all readers within the newsgroup who may be interested.

I love putting together sample files!

My dates are in Row 47 from L47 to CQ47, including quarters. The data I'm
trying to look up and sum is located 7 rows below in Row 53. I need to sum
the numbers between L53 (31-Dec-04) and BO53 (31-Mar-08) and divide the
total
by 2.

I will be entering the date range in CU46 (31-Dec-04) and CV56 (31-Mar-08).
This range is variable so the formula must read the start date in CU46 and
the end date in CV56.

Sample file:

Sample_Sumif.xls 14.5KB

http://cjoint.com/?hyc3RiK7dG

Biff

 

[Guest]

.. Sample file:
Sample_Sumif.xls 14.5KB
http://cjoint.com/?hyc3RiK7dG

Thanks for that!

Here's just a quick rendition of your sample with both suggestions thrown-in:
http://cjoint.com/?hydHHzonve
TomCat_1.xls

I did dress up the cell refs for your shorter equation (it's still shorter,

.. I will be doing this function for all 133 rows below Row 47.

which implied he'd want the formula in ready-to-copy-down form

Looking at your sample, I just realized I had earlier mistaken the EOM
enddate's cell ref as CV46, when it should be CV56 (as posted by OP). Then
again, perhaps there was a typo in the OP, maybe what was mistaken was
correct <g>. Cheers.

---