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

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

"TomCat" wrote in message
...
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

I forgot something!

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

AND the numbers are in the range A2:L2.

Biff

"Biff" wrote in message
...
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

"TomCat" wrote in message
...
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

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

"Biff" wrote:

I forgot something!

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

AND the numbers are in the range A2:L2.

Biff

"Biff" wrote in message
...
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

"TomCat" wrote in message
...
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

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 ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"TomCat" wrote:
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

Sorry, Biff. Didn't work.

"Biff" wrote:

I forgot something!

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

AND the numbers are in the range A2:L2.

Biff

"Biff" wrote in message
...
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

"TomCat" wrote in message
...
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,

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

"Biff" wrote:

I forgot something!

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

AND the numbers are in the range A2:L2.

Biff

"Biff" wrote in message
...
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

"TomCat" wrote in message
...
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

Re my response in the other branch ..

Try this adaptation in say, CT53:
=IF(OR($CU$46="",$CV$46=""),"",SUM(OFFSET(K53,,MAT CH($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

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"TomCat" wrote:
....
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.

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!

"Max" wrote:

Re my response in the other branch ..

Try this adaptation in say, CT53:
=IF(OR($CU$46="",$CV$46=""),"",SUM(OFFSET(K53,,MAT CH($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

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"TomCat" wrote:
...
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.

Try this:

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

Biff

"TomCat" wrote in message
...
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

"Biff" wrote:

I forgot something!

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

AND the numbers are in the range A2:L2.

Biff

"Biff" wrote in message
...
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

"TomCat" wrote in message
...
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