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):

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[TD]Jun[/TD]
[/TR]
[TR]
[TD]Z Ltd[/TD]
[TD]36[/TD]
[TD]24[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Y Ltd[/TD]
[TD][/TD]
[TD][/TD]
[TD]32[/TD]
[TD]31[/TD]
[TD]2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Z Ltd[/TD]
[TD][/TD]
[TD]43[/TD]
[TD]35[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]A Ltd[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

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
Jan	Feb	Mar	Apr	May	Jun	Sum excluding first two values
Z Ltd
Y Ltd
Z Ltd
A Ltd

<colgroup><col style="width: 25pxpx"><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"][/TD]

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

[TD="align: right"]36[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]18[/TD]

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

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]

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

[TD="align: right"][/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]8[/TD]

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

[TD="align: right"]9[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array 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] "]I2[/TH]
[TD="align: left"]{=SUM(INDEX($B2:$G2,MATCH(FALSE,ISBLANK($B2:$G2),0)+2):$G2)}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



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]

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[TD]

H​

[/TD]
[TD]

I​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD][/TD]
[TD]

Jan​

[/TD]
[TD]

Feb​

[/TD]
[TD]

Mar​

[/TD]
[TD]

Apr​

[/TD]
[TD]

May​

[/TD]
[TD]

Jun​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]Z Ltd[/TD]
[TD]

36​

[/TD]
[TD]

24​

[/TD]
[TD]

3​

[/TD]
[TD]

4​

[/TD]
[TD]

6​

[/TD]
[TD]

5​

[/TD]
[TD][/TD]
[TD]

60​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]Y Ltd[/TD]
[TD][/TD]
[TD][/TD]
[TD]

32​

[/TD]
[TD]

31​

[/TD]
[TD]

2​

[/TD]
[TD]

3​

[/TD]
[TD][/TD]
[TD]

0​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]Z Ltd[/TD]
[TD][/TD]
[TD]

43​

[/TD]
[TD]

35​

[/TD]
[TD]

4​

[/TD]
[TD]

3​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD]

43​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]A Ltd[/TD]
[TD]

9​

[/TD]
[TD]

10​

[/TD]
[TD]

1​

[/TD]
[TD]

1​

[/TD]
[TD]

2​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD]

19​

[/TD]
[/TR]
</tbody>[/TABLE]


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

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[TD]

H​

[/TD]
[TD]

I​

[/TD]
[TD]

J​

[/TD]
[TD]

K​

[/TD]
[TD]

L​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD][/TD]
[TD]

Jan​

[/TD]
[TD]

Feb​

[/TD]
[TD]

Mar​

[/TD]
[TD]

Apr​

[/TD]
[TD]

May​

[/TD]
[TD]

Jun​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]Z Ltd[/TD]
[TD]

36​

[/TD]
[TD]

24​

[/TD]
[TD]

3​

[/TD]
[TD]

4​

[/TD]
[TD]

6​

[/TD]
[TD]

5​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Z Ltd[/TD]
[TD]

103​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]Y Ltd[/TD]
[TD][/TD]
[TD][/TD]
[TD]

32​

[/TD]
[TD]

31​

[/TD]
[TD]

2​

[/TD]
[TD]

3​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Y Ltd[/TD]
[TD]

0​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]Z Ltd[/TD]
[TD][/TD]
[TD]

43​

[/TD]
[TD]

35​

[/TD]
[TD]

4​

[/TD]
[TD]

3​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]A Ltd[/TD]
[TD]

19​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]A Ltd[/TD]
[TD]

9​

[/TD]
[TD]

10​

[/TD]
[TD]

1​

[/TD]
[TD]

1​

[/TD]
[TD]

2​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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.