Add numbers with a DATE as one of the conditions

  • Thread starter Thread starter Barbara
  • Start date Start date
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
 
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
 
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?
 
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
 
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!
 
Back
Top