function returning SUM of 2 adjacent cols in 2 separate cells

[CAT]

Hi everyone, I need some help with this please; I am using Excell 2007

Worksheet contains 6 cols, T,U,V,W,X and Y and another col AG in a small
recap table a few columns outside the main worksheet.
Col W contains a formula (copied and pasted right down the col):
=IF(T2="YES",(U2)*V2," ") returning a + figure.
Col X contains a formula (again copied and pasted right down the col):
=IF(T2="NO",-V2," ") returning a - figure.
Col Y contains a formula (in one cell only at the end of a variable series
of row (ranging from 4 to 25+) =SUM(W2:W12)+(SUM(X2:X12) returning the total
of cols W and X for that particular block of rows (in this instance,
positioned in row Y12).
Cols T, U and V are blanks and data is entered as needed which prompts cols
W, X and Y to return a value.
Example:
Col T Col U Col V Col W Col X Col Y
Col AG
Row 2 blank blank blank blank blank blank
2600
Row 3 Yes 30 100 3000 blank blank
2000
Row 4 No blank 450 blank -450 blank
Row 5 blank blank blank blank blank blank
Row 6 Yes 50 20 100 blank blank
Row 7 No blank 50 blank -50 2600
Row 8 blank blank blank blank blank blank
etc
etc
Row 18 yes 100 20 2000 blank 2000
etc
etc
And so on, with a total in col Y for each "block" of data.

What I am doing:
Copy and paste my formulae in cols W and X; then, manually counting the
total of rows in each block, enter the SUM formula in the "framed" cell in
col Y, at the end of each sequence (which can vary from 4 rows to 25 or more);
I then make a note of the cell row number and enter an =Y... in column AG of
the recap table (for instance in the example above, in AG2, I will enter:
=Y7; in AG3, I will enter =Y18, and so on for each block, in AG4, AG5, etc.).
I hope this is clear !

What I would like:
Is to automate the process so that the SUM total of cols W and X will be
calculated in the appropriate cell in col Y and be replicated in col AG -
without having to resort to the manual process. Is this possible?
The amount or rows in each block is randomly different - Data is imported
and sorted chronologically before copying into worksheet.

Thank you very much for any help

 

[Max]

Here's some thoughts for the 2nd Q in your post, ie a simple play to
dynamically extract col Y's interspersed results directly into col AG, all
neatly bunched at the top as desired

In AF2: =IF(Y2="","",ROW())
Leave AF1 empty

In AG2:
=IF(ROWS($1:1)>COUNT(AF:AF),"",INDEX(Y:Y,SMALL(AF:AF,ROWS($1:1))))
Select AF2:AG2, copy down to cover the max expected extent of col Y, say
down to AG200? AG2 will return the required results from col Y, all neatly
packed at the top. Minimize/hide away col AF.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,300 Files:361 Subscribers:57
xdemechanik
---

 

[CAT]

Thank you very much Max. I will give it a try. So If I have understood
correctly, I type the short formula in AF2, leave AF1 empty; type the long
formula in AG2, highlight both AF2 and AG2 and copy down the column (it will
be a max of 50); is that correct?
It now leaves me with the (possible?) automated SUM calculation in column Y,
before I can try yours in my recap table.
Thank you very much for your time and I hope you have a good day.

 

[Max]

Yes, but please don't re-type. Just copy direct from my posting the formulas
and paste directly into the respective cells' formula bars. If you re-type,
you're wasting effort and likely to have typos creep-in, etc. The formulas as
posted have been tested here and should work fine for you over there.

I didn't venture any thoughts on your 1st Q as I could not figure out how's
the association between your data cols T - V and where the auto sum is needed
for each "block". It's something you do easily manually (albeit tiresome) but
it may not be as easy to get Excel to do likewise. If nobody else pops by
here, you could try putting in a new posting on this issue, with perhaps more
elaboration.

Lastly pl take a moment to press the "Yes" button (like the ones below) in
all responses which help
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,300 Files:361 Subscribers:57
xdemechanik

 

[Max]

Ah, I just realized that you posted a similar question in .misc, and you've
got response there as well. Pl refrain from doing this "multi-posting". Stick
to one posting in one particular newsgroup. Most of the regular responders
will read the popular newsgroups (such as this, .misc & .newusers) so you
won't lose out catching their attention by posting only once in one of these
newsgroups.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,300 Files:361 Subscribers:57
xdemechanik
---

 

[CAT]

Hi Max,
I'm new here; so apologies for the "error of conduct". I actually posted on
General and did not get an answer as such, but a need for more info, so I
thought that posting my query on Functions would get me somewhere.
I just wanted to say a big thank you for your answer by the way: I have just
applied your formulae to my present workbook, filled in the Y cells manually
and it WORKS - Every totals are returned in neat order in my checkup table,
including the header of col Y! Thank you again for your help.
Any chance of resolving part 1 of my query?
Sorry to be so demanding!

 

[Max]

Welcome, but pl press the "Yes" button below
since the responses helped, won't you?

On your Q1, I've explained it in my earlier response.
I don't have any further views
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,300 Files:361 Subscribers:57
xdemechanik

 

[CAT]

Hi Max,
Sorry again, I hadn't read your preceding reply. The reason I did not press
Yes is because of the unsolved part 1 of my query. I will wait a little and
if I don't get an answer, will close this thread and start another whilst
trying to clarify things a bit.
Thank you again.