sum last 12 values

[wasntme]

Hello,

I have a spreadsheet in which is added data in 2 columns each month at the end of the sheet, called "NET" and "VAT". I am trying to write a formula that sums entries from last 12 columns "NET" (one formula for last 12 "NET" columns, f.e AK, AI, AG, AE etc) and "VAT" (another formula for last 12 "VAT" columns, f.e AL, AJ, AH, AF etc) (yearly review) no matter how many they are.

I am using for similar purpose (summing last 12 entries, but here is added just one column monthly) this formula:

=ROUND(SUM(INDEX(I15:ZT15,MATCH(9^9,I$12:ZT$12)-11):ZT15),2)

but do not understand it well enough to rework it for my needs.

Any help is greatly appreciated.

 

[navic]

Any help is greatly appreciated.

If I understand you try this formulas below

In 'A3' cell, helper formula (Related to the range in Row 12, return the start column from which the sum starts)

=CHAR(64+(MAX(COLUMN(I15:ZT15)*(I15:ZT15<>""))-24)+1)&(ROW(A15)-3)

In 'B3' cell, helper formula (Related to the range in Row 15, return the start column from which the sum starts.)

=CHAR(64+(MAX(COLUMN(I15:ZT15)*(I15:ZT15<>""))-24)+1)&ROW(A15)

In 'D3' cell, SUM formula for last 12 Cells based on 'COL' header (copy across)

=SUM(IF(INDIRECT($A$3&":ZT12")=D$2;INDIRECT($B$3&":ZT15");0))

I hope it helped.

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

AC

AD

AE

AF

AG

AH

AI

AJ

AK

AL

AM

AN

AO

AP

AQ

AR

AS

AT

AU

AV

1

2

1st col

1st col

VAT

NET

3

V12

V15

12

13

4

5

6

7

8

9

10

11

12

Title

VAT

NET

VAT

NET

VAT

NET

VAT

NET

VAT

NET

VAT

NET

VAT

NET

VAT

NET

VAT

NET

VAT

NET

VAT

NET

VAT

NET

VAT

NET

VAT

NET

VAT

NET

VAT

NET

VAT

NET

VAT

NET

VAT

NET

VAT

13

14

15

aaa

1

1

1

1

1

1

1

1

1

1

1

1

1

1

1

1

1

1

1

1

1

1

1

1

1

1

1

1

1

1

1

1

1

1

1

2

1

16

<tbody>
</tbody>

[EDIT]:
I apologize, I forgot to mention that all ARRAY formulas.