Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 183
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,081
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
IF function which can find a blank cell owl37 Excel Worksheet Functions 5 October 19th 06 05:48 PM
How Do I find/replace a blank cell with a formula? Alvin Excel Worksheet Functions 2 June 23rd 06 08:25 PM
find the first and last non blank cell in a row Allan from Melbourne Excel Discussion (Misc queries) 3 May 19th 06 01:12 PM
Find first non-blank or non-zero in a column of data DerbyJim Excel Discussion (Misc queries) 3 January 16th 06 12:50 PM
Find a non-blank cell and bring back text a in same row Rod Excel Worksheet Functions 12 March 24th 05 10:43 PM


All times are GMT +1. The time now is 12:46 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"