Book1 | |||||||||
---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | |||
21 | |||||||||
22 | Name | (Y/N) | Name | Count | Formula | ||||
23 | Sally | Y | Sally | 2 | -2 | ||||
24 | Mark | N | Mark | 3 | 3 | ||||
25 | James | Y | Mark | 3 | 3 | ||||
26 | Sue | N | Mark | 3 | 3 | ||||
27 | Tom | N | Sue | 4 | 4 | ||||
28 | Sally | 2 | -2 | ||||||
29 | Tom | 4 | 4 | ||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
G23:G29 | G23 | =IF(VLOOKUP(E23,$B$23:$C$27,2,0)="Y",-1,1)*F23 |
Is this immune to me adding or moving columns? Any way with sumifs?Not sure what you mean by scalable, the formula can be copied down. But, try this, and you may need to reverse signs in the formula as you are not specific on what value is to be times 1 or times -1.:
Book1
A B C D E F G 21 22 Name (Y/N) Name Count Formula 23 Sally Y Sally 2 -2 24 Mark N Mark 3 3 25 James Y Mark 3 3 26 Sue N Mark 3 3 27 Tom N Sue 4 4 28 Sally 2 -2 29 Tom 4 4 Sheet1
Cell Formulas Range Formula G23:G29 G23 =IF(VLOOKUP(E23,$B$23:$C$27,2,0)="Y",-1,1)*F23
=Index($C$23:$C$27,match(e23,$B$23:$B$27,0))