• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formula to add multiple columns based on One Criterea

Hello,

I need to sum multiple columns based on selection of range dictated by Date in D1 cell. All dates are in F2:O2.

So, if I put in D1 June 30, 2018, the sum in Column D3: D11 should select data from F3 to I3.

30-Jun

F

G

H

I

J

K

L

M

N

O

Summary

31-Mar

30-Apr

31-May

30-Jun

31-Jul

31-Aug

30-Sep

31-Oct

30-Nov

31-Dec

55

10

15

10

20

30

30

30

30

30

30

59

11

16

11

21

31

31

31

31

31

31

63

12

17

12

22

32

32

32

32

32

32

67

13

18

13

23

33

33

33

33

33

33

71

14

19

14

24

34

34

34

34

34

34

75

15

20

15

25

35

35

35

35

35

35

79

16

21

16

26

36

36

36

36

36

36

83

17

22

17

27

37

37

37

37

37

37

87

18

23

18

28

38

38

38

38

38

38
 
Just to demonstrate there are non-standard ways of working within Excel!

The formula is
= SUM(Data[@] TimeSlice),
that is SUM the current row of the table. The catch is that the rows are intersected with a collection of columns named 'TimeSlice'. This temporal slice
= OFFSET( Data, 0, 0, ,MATCH(date, Header) )
cuts across all the records singling out the terms you require as a range.

p.s. The grid and the sheet headings are not shown because they are not relevant to the solution. They may be displayed by checking boxes in the view ribbon tab if that provides comfort.
 

Attachments

  • Sum_Criterea (PB).xlsx
    11.7 KB · Views: 4
Back
Top