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

[Guest]

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

 

[userofexcel]

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?

 

[Fred Smith]

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(TODAY()),1),A1:A365<=TODAY(),B1:B365)

 

[Arvi Laanemets]

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.

 

[Guest]

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]

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(TODAY()),1),A1:A365<=DATE(YEAR(TODAY()),MONTH(TODAY()),C1),B1:B365)/C1