Formula to exclude first two values

[BiggusDoggus]

Hi all

So I have a table of data (example below - consider the table as starting in A1):

	Jan	Feb	Mar	Apr	May	Jun
Z Ltd	36	24	3	4	6	5
Y Ltd			32	31	2	3
Z Ltd		43	35	4	3	1
A Ltd	9	10	1	1	2	1

<tbody>
</tbody>

What I would like please is a formula that takes either the sum or average (doesn't matter which) for each row, but excluding the first 2 values. In essence, the first 2 numbers are during a trial period, so only want to look at the numbers after the first two months for each company.

I could do this manually, except my actual table is quite large, and is updated regularly.

Is it possible please?

 

[wideboydixon]

Book1
	A	B	C	D	E	F	G	H	I
1		Jan	Feb	Mar	Apr	May	Jun		Sum excluding first two values
2	Z Ltd	36	24	3	4	6	5		18
3	Y Ltd			32	31	2	3		5
4	Z Ltd		43	35	4	3	1		8
5	A Ltd	9	10	1	1	2	1		5
Sheet1

Cell Formulas
Range	Formula
I2	{=SUM(INDEX($B2:$G2,MATCH(FALSE,ISBLANK($B2:$G2),0)+2):$G2)}
Press CTRL+SHIFT+ENTER to enter array formulas.

WBD

 

[BiggusDoggus]

	A	B	C	D	E	F	G	H	I
1		Jan	Feb	Mar	Apr	May	Jun		Sum excluding first two values
2	Z Ltd	36	24	3	4	6	5		18
3	Y Ltd			32	31	2	3		5
4	Z Ltd		43	35	4	3	1		8
5	A Ltd	9	10	1	1	2	1		5

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Array Formulas Cell Formula I2 {=SUM(INDEX($B2:$G2,MATCH(FALSE,ISBLANK($B2:$G2),0)+2):$G2)} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

WBD

Awesome - works perfectly. Thank you!

 

[BiggusDoggus]

Hi again.

Following up from the above, what would be the formula to get the sum of the first 2 months for each client please?

 

[MARZIOTULLIO]

	A​	B​	C​	D​	E​	F​	G​	H​	I​
1​		Jan​	Feb​	Mar​	Apr​	May​	Jun​
2​	Z Ltd	36​	24​	3​	4​	6​	5​		60​
3​	Y Ltd			32​	31​	2​	3​		0​
4​	Z Ltd		43​	35​	4​	3​	1​		43​
5​	A Ltd	9​	10​	1​	1​	2​	1​		19​

<tbody>
</tbody>

I2=SUMPRODUCT((MONTH($B$1:$G$1&0)<=MONTH($C$1&0))*(B2:G2)) copy down

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

1​

Jan​

Feb​

Mar​

Apr​

May​

Jun​

2​

Z Ltd

36​

24​

3​

4​

6​

5​

Z Ltd

103​

3​

Y Ltd

32​

31​

2​

3​

Y Ltd

0​

4​

Z Ltd

43​

35​

4​

3​

1​

A Ltd

19​

5​

A Ltd

9​

10​

1​

1​

2​

1​

<tbody>
</tbody>

L2=SUMPRODUCT((MONTH($B$1:$G$1&0)<=MONTH($C$1&0))*($B$2:$G$5)*($A$2:$A$5=$K2)) copy down

 

[Aladin Akyurek]

Hi again.

Following up from the above, what would be the formula to get the sum of the first 2 months for each client please?

In J2 control+shift+enter, not just enter, and copy down:

=SUM(IF(COLUMN(B2:G2)<=SMALL(IF(ISNUMBER(B2:G2),COLUMN(B2:G2)),2),B2:G2))

 

[wideboydixon]

Using the same logic as my original solution it would be:

{=SUM($B2:INDEX($B2:$G2,MATCH(FALSE,ISBLANK($B2:$G2),0)+1))}

WBD

 

[markmzz]

Hi!

Another way (with WBD's layout):

In I2 and copy down

=SUM(OFFSET(G2,,,,2-COUNT(B2:G2)))

In J2 and copy down

=SUM(B2:G2)-SUM(OFFSET(G2,,,,2-COUNT(B2:G2)))

Or

=SUM(B2:G2)-I2​

Markmzz

 

[BiggusDoggus]

Thanks for all the help guys - went with

=SUM(IF(COLUMN(B2:G2)<=SMALL(IF(ISNUMBER(B2:G2),COLUMN(B2:G2)),2),B2:G2))

Works great.

 

[Aladin Akyurek]

Thanks for all the help guys - went with

=SUM(IF(COLUMN(B2:G2)<=SMALL(IF(ISNUMBER(B2:G2),COLUMN(B2:G2)),2),B2:G2))

Works great.

You are welcome. Thanks for the update.