Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find 1st blank cell in column & sum to the same row in another col
I am trying to come up with a formula that will accomplish the following.....
I have 2 columns that compare the current year weekly data to prior year's weekly data. I need a formula that will sum Column D to same week in Column B. Each week, the next row will be updated in Column B and the cells summed in Column D need to change to include the next week's data too. So for the table below, I would come up with the figure of 350 and the next week when B4 has data input, the total for Column D would be 500. I am trying to compare total inventory each week. The cells in Column B with no data are blank. Any ideas? A B C D 1 week ending 1-6-07 150 wek ending 1-7-06 200 2 week ending 1-13-07 100 week ending 1-14-06 100 3 week ending 1-20-07 75 week ending 1-21-06 50 4 week ending 1-27-07 week ending 1-28-06 150 5 week ending 2-3-07 week ending 2-4-06 225 6 -- Sharon |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find 1st blank cell in column & sum to the same row in another col
Assuming your data starts in row 2, and that the only entries would be the
numbers you want to sum =sum(offset(d2,0,0,count(b2:b20),1)) Change B2:B20 so that it includes all the rows of possible weekly data. Do not include the row with the sum formula "Sharon" wrote: I am trying to come up with a formula that will accomplish the following..... I have 2 columns that compare the current year weekly data to prior year's weekly data. I need a formula that will sum Column D to same week in Column B. Each week, the next row will be updated in Column B and the cells summed in Column D need to change to include the next week's data too. So for the table below, I would come up with the figure of 350 and the next week when B4 has data input, the total for Column D would be 500. I am trying to compare total inventory each week. The cells in Column B with no data are blank. Any ideas? A B C D 1 week ending 1-6-07 150 wek ending 1-7-06 200 2 week ending 1-13-07 100 week ending 1-14-06 100 3 week ending 1-20-07 75 week ending 1-21-06 50 4 week ending 1-27-07 week ending 1-28-06 150 5 week ending 2-3-07 week ending 2-4-06 225 6 -- Sharon |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find 1st blank cell in column & sum to the same row in another col
Here's the non-volatile version:
=IF(COUNT(B1:B5),SUM(D1:INDEX(D1:D5,COUNT(B1:B5))) ,0) Biff "Sharon" wrote in message ... I am trying to come up with a formula that will accomplish the following..... I have 2 columns that compare the current year weekly data to prior year's weekly data. I need a formula that will sum Column D to same week in Column B. Each week, the next row will be updated in Column B and the cells summed in Column D need to change to include the next week's data too. So for the table below, I would come up with the figure of 350 and the next week when B4 has data input, the total for Column D would be 500. I am trying to compare total inventory each week. The cells in Column B with no data are blank. Any ideas? A B C D 1 week ending 1-6-07 150 wek ending 1-7-06 200 2 week ending 1-13-07 100 week ending 1-14-06 100 3 week ending 1-20-07 75 week ending 1-21-06 50 4 week ending 1-27-07 week ending 1-28-06 150 5 week ending 2-3-07 week ending 2-4-06 225 6 -- Sharon |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF function which can find a blank cell | Excel Worksheet Functions | |||
How Do I find/replace a blank cell with a formula? | Excel Worksheet Functions | |||
find the first and last non blank cell in a row | Excel Discussion (Misc queries) | |||
Find first non-blank or non-zero in a column of data | Excel Discussion (Misc queries) | |||
Find a non-blank cell and bring back text a in same row | Excel Worksheet Functions |