Add numbers with a DATE as one of the conditions

[Barbara]

Good morning to all,

I am trying to add a column with one of the conditions being a DATE. Now,
what I would like to do is to be able to add the numbers that all have the
same MONTH, regardless of the day. Also, I am writting this MONTH in a cell
as too be able to look up any month I want.

The formula I have right now is working, but only calculating the actual
DATE that is on sheet 2. The date on sheet two is displayed as MMMM-YYYY,
but the back structure of it is mm/dd/yy. So it is calculating only the 1
sept 09.

Here is a copy of the formula for your review
=SUM(IF(Total!$B$6:$B$5000=D2,IF(Total!$A$6:$A$5000="p",(SUM(Total!$L$6:$L$5000)/SUM(Total!G6:G5000)),0),0))

Worksheet 1

Sale DATE Cwt Sales Freight FOB
Kind Amount

01-Jul-09 300.00 3900.00 $450.00 $3,450.00
02-Jul-09 200.00 5750.00 $600.00 $5,150.00
03-Jul-09 0.50 15.00 $- $15.00
02-Aug-09 1.00 27.00 $- $27.00
03-Aug-09 1.50 40.50 $- $40.50
p 04-Aug-09 96 $2,400.00 $- $2,400.00
01-Sep-09 126.00 4536.00 $250.00 $4,286.00
03-Sep-09 144.00 3024.00 $300.00 $2,724.00
s 04-Sep-09 25 175.00 $- $175.00


Worksheet 2

Monthly Sales

Month September-2009

FOB/cwt 0.00
Freight/cwt 0.00
TOTAL 0.00

Thank you for your help and have a lovely day

Barbara

 

[Jacob Skaria]

Replace
SUM(IF(Total!$B$6:$B$5000=D2
with
SUM(IF(TEXT(Total!$B$6:$B$5000,"mmyy")=TEXT(D2,"mmyy").....


PS:
=SUM(IF(TEXT(Total!$B$6:$B$5000,"mmyy")=TEXT(D2,"mmyy"),IF(Total!$A$6:$A$5000="p",(Total!$L$6:$L$5000)/Total!G6:G5000)))

If this post helps click Yes

 

[Barbara]

Hi Jacob,

That did not completly helped. Now, this formula is adding the whole
"kit-and-boodle"; july, august, sept, oct.... all together! Better then just
the one DATE, but too much now! Ahhh, the joys of formulas!

Would there be another way?

 

[Jacob Skaria]

Please note that this is an array formula except you press CTRL+SHIFT+ENTER
to enter the formula. If successful in 'Formula Bar' you can notice the curly
braces at both ends like "{=<formula>}"

If this post helps click Yes

 

[Barbara]

OOhhhhhh I got it. I had not taken your version of the whole formula. just
replace the beginning. Whole other structure.

Thank you, thank you, thank you!

 

[Jacob Skaria]

Cheers..

If this post helps click Yes