<sigh> how to do a lookup??

[HammerJoe]

Hi,

I feel a bit frustrated with excell right now.
On my previous post I asked about how to forecast using the Excell
functions and someone sugested keeping past data behond current month
and to do an average from it.

I like the idea but now I have hit a wall because I cant get Excell to
do a simple lookup.

Here is the situation:

-----A----------b
-2--Oct/30---3
-3--Oct/31---4
-4--Nov/01----3
-5--Nov/03----2
...and so on.

What I need to achieve is to get the sum of columbn B since the
beginning of Nov, in this example it would be sum(b4;b5).
This if the table was static, I want to keep a certain number of rows
(usually 21) and move it up by one everyday, thus replacing a2 row with
a3 info, and adding new info on the last row.
I want to keep track of the total tasks for Nov so for that I need to
find out what row is the first entry for november.
Today it is A4 but tomorrow it will be moved to A3.
I cant make any of the excell functions to work.
I have A coulum formate for date to show month and day only, and I
enter month and day only info into the cell.

I cant get any lookup function to work.

What am I doing wrong?

Thanks

 

[Max]

One possibility ..

Assuming real dates are entered within A2:A50, corresponding values within
B2:B50
Place in say, B1:
=SUMPRODUCT(--(MONTH(INDIRECT("A2:A50"))=MONTH(TODAY())),INDIRECT("B2:B50"))
B1 will return the required sum of numbers within B2:B50 for the current
month. The INDIRECT will ensure that the formula always cover the said
ranges, irrespective of row deletions, movements etc within A2:B50 that may
occur with daily updating.

 

[Ron Coderre]

If you want to add all of the Col_B items that correspond to Col_A dates that
are equal to, or greater than, Nov 01, 2006 then...
try this:

=SUMIF(A:A,">="&DATE(2006,11,1),B:B)

or this variation
=SUMIF(A:A,">=11/1/2006",B:B)

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

 

[bobocat]

Do you want to find the sum of November?
if yes, you can try
=sumproduct((month(A2:A22)=11)*(B2:B22))

 

[Max]

And if there's ever the risk of stray numbers creeping in within B2:B50 for
which there are no dates entered as yet within A2:A50, use this more robust
version:
=SUMPRODUCT((MONTH(INDIRECT("A2:A50"))=MONTH(TODAY()))*(INDIRECT("A2:A50")<>""),INDIRECT("B2:B50"))

 

[HammerJoe]

I knew it.

I knew there would be a simple way of doing things.
Thanks for the replies, it is awesome.

Now I can also count the number of rows for the month as well with
sumproduct.

Awesome stuff...

Thanks agains.

 

[Max]

You're welcome !