| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Add numbers with a DATE as one of the conditions
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$500 0="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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Add numbers with a DATE as one of the conditions
Replace
SUM(IF(Total!$B$6:$B$5000=D2 with SUM(IF(TEXT(Total!$B$6:$B$5000,"mmyy")=TEXT(D2,"mm yy")..... PS: =SUM(IF(TEXT(Total!$B$6:$B$5000,"mmyy")=TEXT(D2,"m myy"),IF(Total!$A$6:$A$5000="p",(Total!$L$6:$L$500 0)/Total!G6:G5000))) If this post helps click Yes --------------- Jacob Skaria "Barbara" wrote: 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$500 0="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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Add numbers with a DATE as one of the conditions
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" wrote: Replace SUM(IF(Total!$B$6:$B$5000=D2 with SUM(IF(TEXT(Total!$B$6:$B$5000,"mmyy")=TEXT(D2,"mm yy")..... PS: =SUM(IF(TEXT(Total!$B$6:$B$5000,"mmyy")=TEXT(D2,"m myy"),IF(Total!$A$6:$A$5000="p",(Total!$L$6:$L$500 0)/Total!G6:G5000))) If this post helps click Yes --------------- Jacob Skaria "Barbara" wrote: 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$500 0="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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Add numbers with a DATE as one of the conditions
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 --------------- Jacob Skaria "Barbara" wrote: 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" wrote: Replace SUM(IF(Total!$B$6:$B$5000=D2 with SUM(IF(TEXT(Total!$B$6:$B$5000,"mmyy")=TEXT(D2,"mm yy")..... PS: =SUM(IF(TEXT(Total!$B$6:$B$5000,"mmyy")=TEXT(D2,"m myy"),IF(Total!$A$6:$A$5000="p",(Total!$L$6:$L$500 0)/Total!G6:G5000))) If this post helps click Yes --------------- Jacob Skaria "Barbara" wrote: 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$500 0="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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Add numbers with a DATE as one of the conditions
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" wrote: 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 --------------- Jacob Skaria "Barbara" wrote: 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" wrote: Replace SUM(IF(Total!$B$6:$B$5000=D2 with SUM(IF(TEXT(Total!$B$6:$B$5000,"mmyy")=TEXT(D2,"mm yy")..... PS: =SUM(IF(TEXT(Total!$B$6:$B$5000,"mmyy")=TEXT(D2,"m myy"),IF(Total!$A$6:$A$5000="p",(Total!$L$6:$L$500 0)/Total!G6:G5000))) If this post helps click Yes --------------- Jacob Skaria "Barbara" wrote: 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$500 0="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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Add numbers with a DATE as one of the conditions
Cheers..
If this post helps click Yes --------------- Jacob Skaria "Barbara" wrote: 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" wrote: 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 --------------- Jacob Skaria "Barbara" wrote: 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" wrote: Replace SUM(IF(Total!$B$6:$B$5000=D2 with SUM(IF(TEXT(Total!$B$6:$B$5000,"mmyy")=TEXT(D2,"mm yy")..... PS: =SUM(IF(TEXT(Total!$B$6:$B$5000,"mmyy")=TEXT(D2,"m myy"),IF(Total!$A$6:$A$5000="p",(Total!$L$6:$L$500 0)/Total!G6:G5000))) If this post helps click Yes --------------- Jacob Skaria "Barbara" wrote: 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$500 0="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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How can I have 2 date conditions on the same cell? | Excel Discussion (Misc queries) | |||
| How do I sum numbers that match two conditions? | Excel Worksheet Functions | |||
| Random numbers generation with conditions. | Excel Worksheet Functions | |||
| Calculate sum of numbers with conditions | Excel Worksheet Functions | |||
| How to pull numbers from two tables with conditions? | Excel Worksheet Functions | |||
Linear Mode
