# how to reference a specific cell in a formula if other cells match a specific value

#### breezer123

##### New Member
Hi! Thanks so much for looking at my question! I am trying to create a formula that subtracts a specific value from another formula. However, that specific value may change.
Example:
A B C D
1 1 100 =(2000 - ( if A = 1, i want to subtract the C value where B =1))
1 2 250
1 3 310
1 4 .
2 1
2 2 =((2000 - ( if A = 2, i want to subtract the C value where B =1))
2 3
2 4
3 1
3 2
3 3
3 4

Hopefully this makes sense! I am trying to subtract the C value that goes along with the B1 value for each different A.

I was thinking an index match of some sort but wasnt exactly sure how to do that when the A's change. Thanks so much in advance for help!

### Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

#### jasonb75

##### Well-known Member
Please use XL2BB for posting your examples (button on reply toolbar) so that they don't get squashed into a corner.

Assuming headers in row 1, data starting in row 2, try this one in D2 and fill down.

=IF(B2=1,2000-SUMIFS(C\$2:C2,A\$2:A2,A2),"")

#### breezer123

##### New Member
Please use XL2BB for posting your examples (button on reply toolbar) so that they don't get squashed into a corner.

Assuming headers in row 1, data starting in row 2, try this one in D2 and fill down.

=IF(B2=1,2000-SUMIFS(C\$2:C2,A\$2:A2,A2),"")
I apologize and will fix that for the next time I post. In this formula, wouldn't the D column return empty if the B column isn't 1 though?

#### jasonb75

##### Well-known Member
It was only a guess, I couldn't make any sense of your example.

I think we need a correctly formatted example with the expected results in order to make your requirement clearer.

#### breezer123

##### New Member

I apologize for it being confusing. I want it to be one formula for all of column D. For example, in row 2 I would like it to be 250-100. In row 3, 310 - 100. However, when we get to the A =2, I would need it to be C6 - C5. And for A =3, it would be C12 - C9. Does that make sense? Thank you.

#### jasonb75

##### Well-known Member
I think I get it now, I'm guessing that D6 should be 30, not 200 as shown in the image? Try this in D1 and fill down.

=IF(B2=1,0,C2-SUMIFS(C\$1:C1,B\$1:B1,1,A\$1:A1,A1))

#### Fluff

##### MrExcel MVP, Moderator
Another possibility
=C1-INDEX(\$C\$1:\$C\$16,MATCH(A1,\$A\$1:\$A\$16,0))

Replies
3
Views
96
Replies
22
Views
498
Replies
1
Views
92
Replies
3
Views
55
Replies
5
Views
32

1,133,428
Messages
5,658,738
Members
418,467
Latest member
sc356448

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back