Formiua help

[Wilcock1]

Hi,

Please would you be able to help with a formula.

I am trying to bring back the correct result as follows.

1 2 3 4 5 6 7 8 9 10 11 12
Consultancy 10000 10000 5000 6000 4000 5000 1000 2000 3000 2000 3000 5000
Travel 5000 1000 1000 2000 1000 3000 1000 1000 2000 1000 2000 3000
Training 1000 1000 500 1000 500 1000 500 1000 500 500 1000 500

Another table has my criteria of

Consultancy
Travel
Training


Cell A is 12

In the Consultancy cell in the second table I would like to bring back a sum of the columns that are equal to and less than Cell A.

If Cell A is 12 then the result is the sum of 1 to 12 = 56000
If Cell A is 1 then the result is the sum of 1 = 10000
If Cell A is 3 then the result is the sum of 1 to 3 = 25000

Many thanks in advance

Andrew

 

[FormR]

Hi, something like this maybe..

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		1	2	3	4	5	6	7	8	9	10	11	12
2	Consultancy	10000	10000	5000	6000	4000	5000	1000	2000	3000	2000	3000	5000
3	Travel	5000	1000	1000	2000	1000	3000	1000	1000	2000	1000	2000	3000
4	Training	1000	1000	500	1000	500	1000	500	1000	500	500	1000	500
5
6
7		3
8	Consultancy	25000
Sheet1

Cell Formulas
Range	Formula
B8	=SUMIFS(INDEX($B$2:$M$4,MATCH(A8,$A$2:$A$4,0),0),$B$1:$M$1,"<="&$B$7)

 

[AlanY]

something like this?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Consultancy	10000	10000	5000	6000	4000	5000	1000	2000	3000	2000	3000	5000
2	Travel	5000	1000	1000	2000	1000	3000	1000	1000	2000	1000	2000	3000
3	Training	1000	1000	500	1000	500	1000	500	1000	500	500	1000	500
4
5
6
7	Consultancy	12	56000
8	Travel	8	15000
9	Training	4	3500
10
Sheet2

Cell Formulas
Range	Formula
C7	=SUM(OFFSET(A1,0,1,1,B7))

 

[Wilcock1]

Hi, something like this maybe..

Excel 2013/2016

	A	B	C	D	E	F	G	H	I	J	K	L	M
Consultancy
Travel
Training
Consultancy

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]12[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]10000[/TD]
[TD="align: right"]10000[/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"]6000[/TD]
[TD="align: right"]4000[/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]3000[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]3000[/TD]
[TD="align: right"]5000[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]5000[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]3000[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]3000[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]1000[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]500[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]25000[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B8[/TH]
[TD="align: left"]=SUMIFS(INDEX($B$2:$M$4,MATCH(A8,$A$2:$A$4,0),0),$B$1:$M$1,"<="&$B$7)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Hi Thanks so much for your response, that's really helpful! Andrew

 

[Wilcock1]

Hi Thanks so much for your response, that's really helpful!