CODEWITHSUNDEEP
excelvba
Sunny D'Souza
Sunny D'Souza

Reputation: 626

Using SumIf to add cells instead of range of values

I currently have a situation wherein I am required to subtract 2 cells. 1 cell is already known but the second cell is to be found from a range. I have seen this can be done using the SumIf but among the range,criteria,sum_range factor, I dont know how to mention the sum_range as 2 distinct cells.

Also, point to be noted is that these 2 cells needed to be subtracted are in 2 different sheets in the same excel workbook.

I was trying using this below =sumif(AC1:AC3,Sheet1!AA14,Sheet2!S1 - Sheet1!S14)

But it gives error. Can someone help. Am trying to match Sheet1!AA14 with any one of the values in this range AC1:AC3. And for the a perfect match, it would subtract the corresponding sheets "S" cells

please help

Upvotes: 0

Views: 1532

Answers (1)

Gaijinhunter
Gaijinhunter

Reputation: 14685

I suggest you try using VLOOKUP if you have the first value of your subtraction equation. Here is an example (sorry I don't understand your setup enough to use your cell numbers):

A         B       C       D
test      20      15      test
test1     10      

=VLOOKUP(D1,A1:B2,2) - C1    ' results in 5 (20 - 15)
  • Look for value in D1
  • A1:B2 is the range with the value and the data you want back. 1st column is the search column.
  • If it finds the value it will give back the value in the 2nd column of the range, in this case B.
  • You then take the value and subtract the value in C1 from it.

Check out MS's documentation on VLOOKUP and mess around with it. Best of luck.

Upvotes: 1

Related Questions