How do I use LOOKUP to return a range of values, then SUM values?
 

Any thoughts? I have spreadsheet that has this years dates running down
column A. I then have each day's corresponding production figure running down
column B. I want to assign a function to a cell that will result in the
months cumulative production figure. (I realize I can manually select the
desired Column B cells, but this is not the solution I am looking for)

The formula I have come up with so far is:

=SUM(LOOKUP(TEXT(TODAY(),"mmm"),TEXT(A1:A365,"mmm" ),B1:B365))

For some reason the result is 0. I used the formula auditing tool and came
up with nothing. Any thoughts?

Thanks

How do I use LOOKUP to return a range of values, then SUM values?
 

Two questions:
1- why not sum in the talley cell with a simple function?
2- are you talleying the month or the year?

it is not clear in your decription, each month then you total the year?


--
userofexcel
------------------------------------------------------------------------
userofexcel's Profile: http://www.excelforum.com/member.php...o&userid=37040
View this thread: http://www.excelforum.com/showthread...hreadid=568176

How do I use LOOKUP to return a range of values, then SUM values?
 

First, you don't need the Lookup, and second, you want Sumif, not Sum.

However, you're trying to sum based on two selections (greater than the 1st of
the month and less than today). To get around this, you have two choices:

1. Add a helper column, say C, with the month (=text(a2,"mmm")). Then you can
use:
=sumif(C:C,text(today(),"mmm"),B:B)

2. Use Sumproduct, as in:

=SUMPRODUCT(A1:A365=DATE(YEAR(TODAY()),MONTH(TODA Y()),1),A1:A365<=TODAY(),B1:B365)

--
Regards,
Fred


"irvine79" wrote in message
...
Any thoughts? I have spreadsheet that has this years dates running down
column A. I then have each day's corresponding production figure running down
column B. I want to assign a function to a cell that will result in the
months cumulative production figure. (I realize I can manually select the
desired Column B cells, but this is not the solution I am looking for)

The formula I have come up with so far is:

=SUM(LOOKUP(TEXT(TODAY(),"mmm"),TEXT(A1:A365,"mmm" ),B1:B365))

For some reason the result is 0. I used the formula auditing tool and came
up with nothing. Any thoughts?

Thanks

How do I use LOOKUP to return a range of values, then SUM values?
 

Hi

=SUMIF(A1:A365,"=" & TEXT(DATE(2006,n,1),B1:B135) - SUMIF(A1:A365,"<=" &
TEXT(DATE(2006,n+1,0),B1:B135)
, or
=SUMPRODUCT(--(MONTH(A1:A365)=n),B1:B135)

, where n is the number of month.


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )



"irvine79" wrote in message
...
Any thoughts? I have spreadsheet that has this years dates running down
column A. I then have each day's corresponding production figure running
down
column B. I want to assign a function to a cell that will result in the
months cumulative production figure. (I realize I can manually select the
desired Column B cells, but this is not the solution I am looking for)

The formula I have come up with so far is:

=SUM(LOOKUP(TEXT(TODAY(),"mmm"),TEXT(A1:A365,"mmm" ),B1:B365))

For some reason the result is 0. I used the formula auditing tool and came
up with nothing. Any thoughts?

Thanks

How do I use LOOKUP to return a range of values, then SUM valu
 

Fred: You're the man. Thanks for the help. It definitely got me headed in the
right direction. Any thoughts on how I'd average this months daily production
totals from the 1st until any given day of the month using the same sumif
logic?

thanks

"Fred Smith" wrote:

First, you don't need the Lookup, and second, you want Sumif, not Sum.

However, you're trying to sum based on two selections (greater than the 1st of
the month and less than today). To get around this, you have two choices:

1. Add a helper column, say C, with the month (=text(a2,"mmm")). Then you can
use:
=sumif(C:C,text(today(),"mmm"),B:B)

2. Use Sumproduct, as in:

=SUMPRODUCT(A1:A365=DATE(YEAR(TODAY()),MONTH(TODA Y()),1),A1:A365<=TODAY(),B1:B365)

--
Regards,
Fred


"irvine79" wrote in message
...
Any thoughts? I have spreadsheet that has this years dates running down
column A. I then have each day's corresponding production figure running down
column B. I want to assign a function to a cell that will result in the
months cumulative production figure. (I realize I can manually select the
desired Column B cells, but this is not the solution I am looking for)

The formula I have come up with so far is:

=SUM(LOOKUP(TEXT(TODAY(),"mmm"),TEXT(A1:A365,"mmm" ),B1:B365))

For some reason the result is 0. I used the formula auditing tool and came
up with nothing. Any thoughts?

Thanks

How do I use LOOKUP to return a range of values, then SUM valu
 

Sure. Just divide by the day of the month, as in:

=sumif(..)/day(today())

Of, if you wanted to specify the day, say in c1, your average daily production
from the 1st to c1 of the month is:

=SUMPRODUCT(A1:A365=DATE(YEAR(TODAY()),MONTH(TODA Y()),1),A1:A365<=DATE(YEAR(TODAY()),MONTH(TODAY()) ,C1),B1:B365)/C1

--
Regards,
Fred


"irvine79" wrote in message
...
Fred: You're the man. Thanks for the help. It definitely got me headed in the
right direction. Any thoughts on how I'd average this months daily production
totals from the 1st until any given day of the month using the same sumif
logic?

thanks

"Fred Smith" wrote:

First, you don't need the Lookup, and second, you want Sumif, not Sum.

However, you're trying to sum based on two selections (greater than the 1st
of
the month and less than today). To get around this, you have two choices:

1. Add a helper column, say C, with the month (=text(a2,"mmm")). Then you can
use:
=sumif(C:C,text(today(),"mmm"),B:B)

2. Use Sumproduct, as in:

=SUMPRODUCT(A1:A365=DATE(YEAR(TODAY()),MONTH(TODA Y()),1),A1:A365<=TODAY(),B1:B365)

--
Regards,
Fred


"irvine79" wrote in message
...
Any thoughts? I have spreadsheet that has this years dates running down
column A. I then have each day's corresponding production figure running
down
column B. I want to assign a function to a cell that will result in the
months cumulative production figure. (I realize I can manually select the
desired Column B cells, but this is not the solution I am looking for)

The formula I have come up with so far is:

=SUM(LOOKUP(TEXT(TODAY(),"mmm"),TEXT(A1:A365,"mmm" ),B1:B365))

For some reason the result is 0. I used the formula auditing tool and came
up with nothing. Any thoughts?

Thanks