Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumif criteria
I am trying to do a production planning worksheet where I roll forward the
plan on a daily basis. In column A I have entered a list of dates. The first date would be, for example today, I enter 6/28. The rest of the dates are rolled forward by the formula for the cell above + 1. In column K I am trying to total the orders due on the date in column A. The orders are in a separate worksheet in the same workbook. I am trying to use a sumif formula. It works for all the dates after the first date in the list because I am entering (pointing) to the date in column A. However, in the first day I need to sumif all orders that are due on that day or before that date. So, I think I need to enter <=A2 (where A2 is the first date of the roll forward. When I enter it this way the total is 0. I have tried entering both with and without quotes around the formula and this doesn't help. I have changed the date in cell A2 to be a formula =date(2006,6,28) and this does not work. I was able to make it work by entering the critera as "<=6/28/06" however this requires me to go in and physcially change this each time I change the beginning date of the rollforward. Is there not a way to enter the criteria that will change automatically when the date in column A changes? -- Bob B. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumif criteria
Try
"<="&A2 -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Bob B." wrote in message ... I am trying to do a production planning worksheet where I roll forward the plan on a daily basis. In column A I have entered a list of dates. The first date would be, for example today, I enter 6/28. The rest of the dates are rolled forward by the formula for the cell above + 1. In column K I am trying to total the orders due on the date in column A. The orders are in a separate worksheet in the same workbook. I am trying to use a sumif formula. It works for all the dates after the first date in the list because I am entering (pointing) to the date in column A. However, in the first day I need to sumif all orders that are due on that day or before that date. So, I think I need to enter <=A2 (where A2 is the first date of the roll forward. When I enter it this way the total is 0. I have tried entering both with and without quotes around the formula and this doesn't help. I have changed the date in cell A2 to be a formula =date(2006,6,28) and this does not work. I was able to make it work by entering the critera as "<=6/28/06" however this requires me to go in and physcially change this each time I change the beginning date of the rollforward. Is there not a way to enter the criteria that will change automatically when the date in column A changes? -- Bob B. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumif criteria
Unless I am misunderstanding the answer, it didn't work. I put ,"<="&A2, in
the critera and it returned a 0. I tried both typing A2 and pointing to A2 and this did not change the result. I also tried changing the date in A2 to a formula, =date(2006,6,28) and this did not change the result. -- Bob B. "Bob B." wrote: I am trying to do a production planning worksheet where I roll forward the plan on a daily basis. In column A I have entered a list of dates. The first date would be, for example today, I enter 6/28. The rest of the dates are rolled forward by the formula for the cell above + 1. In column K I am trying to total the orders due on the date in column A. The orders are in a separate worksheet in the same workbook. I am trying to use a sumif formula. It works for all the dates after the first date in the list because I am entering (pointing) to the date in column A. However, in the first day I need to sumif all orders that are due on that day or before that date. So, I think I need to enter <=A2 (where A2 is the first date of the roll forward. When I enter it this way the total is 0. I have tried entering both with and without quotes around the formula and this doesn't help. I have changed the date in cell A2 to be a formula =date(2006,6,28) and this does not work. I was able to make it work by entering the critera as "<=6/28/06" however this requires me to go in and physcially change this each time I change the beginning date of the rollforward. Is there not a way to enter the criteria that will change automatically when the date in column A changes? -- Bob B. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumif criteria
=SUMIF(A:A,"<="&A2,B:B) works, just adjust to your columns.
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Bob B." wrote in message ... Unless I am misunderstanding the answer, it didn't work. I put ,"<="&A2, in the critera and it returned a 0. I tried both typing A2 and pointing to A2 and this did not change the result. I also tried changing the date in A2 to a formula, =date(2006,6,28) and this did not change the result. -- Bob B. "Bob B." wrote: I am trying to do a production planning worksheet where I roll forward the plan on a daily basis. In column A I have entered a list of dates. The first date would be, for example today, I enter 6/28. The rest of the dates are rolled forward by the formula for the cell above + 1. In column K I am trying to total the orders due on the date in column A. The orders are in a separate worksheet in the same workbook. I am trying to use a sumif formula. It works for all the dates after the first date in the list because I am entering (pointing) to the date in column A. However, in the first day I need to sumif all orders that are due on that day or before that date. So, I think I need to enter <=A2 (where A2 is the first date of the roll forward. When I enter it this way the total is 0. I have tried entering both with and without quotes around the formula and this doesn't help. I have changed the date in cell A2 to be a formula =date(2006,6,28) and this does not work. I was able to make it work by entering the critera as "<=6/28/06" however this requires me to go in and physcially change this each time I change the beginning date of the rollforward. Is there not a way to enter the criteria that will change automatically when the date in column A changes? -- Bob B. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I use the sumif if I have multiple criteria (i.e. greater . | Excel Worksheet Functions | |||
Multiple SUMIF Criteria | Excel Worksheet Functions | |||
SUMIF with Mutiple Ranges & Criteria | Excel Discussion (Misc queries) | |||
"criteria" in a sumif refering to the value in another cell | Excel Discussion (Misc queries) | |||
Can I use a cell reference in the criteria for the sumif function. | Excel Worksheet Functions |