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

breezer123

New Member
Joined
Jul 13, 2020
Messages
18
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 30, 2008
Messages
12,959
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jul 13, 2020
Messages
18
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 30, 2008
Messages
12,959
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jul 13, 2020
Messages
18
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.
1596390183319.png
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,959
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
58,493
Office Version
  1. 365
Platform
  1. Windows
Another possibility
=C1-INDEX($C$1:$C$16,MATCH(A1,$A$1:$A$16,0))
 

Watch MrExcel Video

Forum statistics

Threads
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?

Disable AdBlock

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
Top