Sum of different lines

[olwy]

Dear all,
after some days not using excel so often my brain hurts trying to remember a solution I created long time ago (and my flash disk is not available for me this month).
I need to summarize YTD number of monthly movement on account listed on more sheets.
more in detail: I need excel make a sum (starting at cell which has same name as in A column on sheet in B column. I need it makes a summation of not all, but just YTD months (=here:offset (0, reported month).

Please give me a hint or a solution and save it in excel as well (I am not using english excel, but saving in file make all needed translation.)

Thank you very much.

Other way: See formulas I created. Make them more automatic. Thanks

Petr

[zbor]

I don't know is there a simplier way...

Please Login or Register  to view this content.

[ben_hensel]

A1 = "Account"
A2 = 10101
//User input: the account number


B1 = Sheet
B2 = IF(ISERROR(MATCH(A2,A!A1:A100,0)), IF(ISERROR(MATCH(A2,B!A1:A100,0)),#N/A,"B"),"A")
//IF: the Account Number is on sheet A and says "A" if it is;
//ELSE: the Account Number is on sheet B and says "B" if it is;
//ELSE: returns an error if neither of the above is true.

C1 = "Row"
C2 = MATCH(A2,INDIRECT(List1!B2&"!A1:A100"),0)
//Returns the row number on the sheet for the account number.


D1 = "Month"
D2 = 9
// User input

E1 = "SUM"
E2 = SUMIF(INDIRECT(B2&"!C2:N2"),"<="&List1!$D2,INDIRECT(B2&"!"&"C"&C2&":L"&C2))
//Takes the SUM of all values in the account row IF the number on the header row is less than or equal to the user input of month.

General note: If your A and B sheets aren't set up exactly the same as each other, AND the same as the example, then this is going to either explode into confusion and errors, or merely lie to you. So you might have to adjust your formulas.

[olwy]

Please, please, paste it in the excel as I wrote. Thanks a lot.

[ben_hensel]

Please, please, paste it in the excel as I wrote. Thanks a lot.

...you can't do this yourself?

w/e

[olwy]

Thank you very much. As I wrote before, I am not using english version of excel and there is a different syntaxt of some function. So, if I only translate it to my language, it does not works. If you save it and I open it - it works very well with no problem. That is the reason for. Again - thanks.
Your solution is working nicely for my needs. Have a nice weekend.