# 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

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

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

• breezer123

#### Fluff

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

• breezer123