# Add numbers with a DATE as one of the conditions

B

#### 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

J

#### 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

B

#### 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?

J

#### 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

B

#### 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!

J

#### Jacob Skaria

Cheers..

If this post helps click Yes