Formula Help please

[lloydie8]

Hi

I'm after a bit of help please...

I'm compiling football stats for my team, and I'm a bit stuck! I have my sheet laid out similar to the table below.

In the overall appears column, I'm trying to add up the 3 apps columns using a formula to return what I've typed in the Target column.

Can someone please help me do this? I have tried building a formula but I am getting a whole bunch of errors depending which column I try working first.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Player[/TD]
[TD]League Apps[/TD]
[TD]Cup 1 Apps[/TD]
[TD]Cup 2 Apps[/TD]
[TD]Overall Apps[/TD]
[TD]Target[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]9[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD][/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]11 (2)[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]12 (2)[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD](2)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](2)[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]15[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]20[/TD]
[/TR]
</tbody>[/TABLE]

Many thanks
Gareth

 

[Yongle]

Keep +ve and -ve numbers in separatecolumns in one table (perhaps in a different sheet) and derive the table you want from that by formula


Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][th]

G​

[/th][th]

H​

[/th][th]

I​

[/th][th]

J​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]Player[/td][td]League
Apps[/td][td]League()[/td][td]Cup 1
Apps[/td][td]Cup1()[/td][td]Cup 2
Apps[/td][td]Cup2()[/td][td]Overall
Apps +[/td][td]OverallApps ()[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]A[/td][td]

9​

[/td][td][/td][td]

1​

[/td][td][/td][td]

3​

[/td][td][/td][td]

13​

[/td][td]

0​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]B[/td][td]

11​

[/td][td]

2​

[/td][td][/td][td][/td][td]

1​

[/td][td][/td][td]

12​

[/td][td]

2​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]C[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

0​

[/td][td]

0​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]D[/td][td][/td][td]

2​

[/td][td][/td][td][/td][td][/td][td][/td][td]

0​

[/td][td]

2​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td]E[/td][td]

15​

[/td][td][/td][td]

3​

[/td][td][/td][td]

2​

[/td][td][/td][td]

20​

[/td][td]

0​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td]Player[/td][td]League
Apps[/td][td]Cup 1
Apps[/td][td]Cup 2
Apps[/td][td]Overall
Apps[/td][td][/td][td][/td][td][/td][td]Cell[/td][td] Formula[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9​

[/td][td]A[/td][td]9[/td][td]1[/td][td]3[/td][td]13[/td][td][/td][td][/td][td][/td][td]B9[/td][td] =IF(B2>0,B2,"")&IF(C2>0,"("&C2&")","")[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10​

[/td][td]B[/td][td]11(2)[/td][td][/td][td]1[/td][td]12(2)[/td][td][/td][td][/td][td][/td][td]C9[/td][td] =IF(D2>0,D2,"")&IF(E2>0,"("&E2&")","")[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

11​

[/td][td]C[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]D9[/td][td] =IF(F2>0,F2,"")&IF(G2>0,"("&G2&")","")[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

12​

[/td][td]D[/td][td](2)[/td][td][/td][td][/td][td](2)[/td][td][/td][td][/td][td][/td][td]E9[/td][td] =IF(H2>0,H2,"")&IF(I2>0,"("&I2&")","")[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

13​

[/td][td]E[/td][td]15[/td][td]3[/td][td]2[/td][td]20[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

14​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet2[/td][/tr][/table]

 

[lloydie8]

Thanks for this, it works a treat.

I think I have been over-complicating things!

 

[Yongle]

Simple is usualy the best way
Thanks for your feedback