Function returning the total of two adjacent columns

C

CAT

Hi everyone, I am re-wording my original query to try an clarify the issue
and hope someone can help:
Data is copied into a workbook starting with Row 7 Cell A7
In the example below:
Col B Col C Col W Col X Col Y
Row 7 1.30A 1 +80
Row 8 2
Row 9 3
Row 10 4 -20
Row 11 5
Row 12 6 -10
Row 13 7 +50
Row 14 2.00B 1
Row 15 2 +60
Row 16 3
Row 17 4 -20
Row 18 5 +40
Row 19 2.30A 1
Row 20 2
Row 21 3 -10
Row 22 4 -10
Row 23 2.45C
Etc. Etc.
What I am doing re the above example is as follows:
Manually copy and paste the formula:
SUM(W7:W13)+SUM(X7:X13) in cell Y13,
one row above new entry in cell B14
SUM(W14:W18)+SUM(X14:X18) in cell Y18,
one row above new entry in cell B19
SUM(W19:W22)+SUM(X19:X22) in cell Y19,
one row above new entry in cell B23
Etc. Etc.
The blocks of data can vary from 4 to 25+ rows and are randomly different.
The "Total" cell with the formula in col Y is always one row above
the new entry in col B.
I hope this is clearer than my previous query and that someone can help me.
I am trying to automate what is essentially a time consuming task.
Thank you all in advance for your help.
 
B

bpeltzer

In Y7: =if(B8="","",SUM(W$7:X7)
In Y8: =if(B9="","",SUM(W$7:X8)-SUM(Y$7:Y7), and copy the formula from Y8
down through the rest of column Y.
Logically, this says that the first row's total is blank unless the next row
has a new entry in column B, in which case the total is just the first row's
sum.
For subsequent rows, the total is blank unless the next row has a new entry
in column B; in that case the total is the total from W and X in all rows,
less the amounts already tallied in prior rows of column Y.
 
C

CAT

Hi Bpeltzer,
Just tested your formulae:
Marvellous!! Thank you so much!

It works right through except for the last one (row 268): but of course I
don't have any more entry in the next row in col B so it won't return a
total, but I can do this one manually; I can live with that, you save me an
awfull lot of time, thank you again.

Just one thing, at the end of formula one, Excell asked me to add a closing
parentheses as in =if(B8="","",SUM(W$7:X7))
And the same again for formula two:
as in =if(B9="","",SUM(W$7:X8)-SUM(Y$7:Y7))

Hope I am not offending you in pointing this out; I'm a total newby re the
writing of
formulae so I wouldn't have known anyway.

Have a great day
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

function returning SUM of 2 adjacent cols in 2 separate cells 7
VLOOKUP Function returning multiple values in a separate table 8
create new table from Multiple Criteria in multiple columns 4
(Advanced Excel) Figuring out a formula of continuous progressiontrying to create 19 Formulas into O 5
how do you total columns that are not adjacent 2
cell returning totals of 2 cols+ sending this value to another col 5
Worksheet Formula Returning Sum of a Series 16
Transpose 4 Columns data to Rows for Employee Time In and Time Out Schedule 0

Top