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
365
Platform
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!
 

Some videos you may like

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
Joined
Dec 30, 2008
Messages
9,996
Office Version
2019
Platform
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
365
Platform
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
9,996
Office Version
2019
Platform
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
365
Platform
Windows
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
9,996
Office Version
2019
Platform
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
42,786
Office Version
365
Platform
Windows
Another possibility
=C1-INDEX($C$1:$C$16,MATCH(A1,$A$1:$A$16,0))
 

Watch MrExcel Video

Forum statistics

Threads
1,102,565
Messages
5,487,591
Members
407,605
Latest member
PACULA

This Week's Hot Topics

Top