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

[breezer123]

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!

 

[jasonb75]

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]

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]

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]

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]

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]

Another possibility
=C1-INDEX($C$1:$C$16,MATCH(A1,$A$1:$A$16,0))