Using SumIf to add cells instead of range of values

0
votes

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

1
excelvba
For starters, you shouldn't be doing the subtraction inside the sumif statement. The third parameter is supposed to be the sum range. But if you have the first value of the equation and all you need to do is find the second, why don't you use INDEX MATCH or VLOOKUP? – aevanko
Is the first already known cell you need to perform the subtraction on the first value and you're going to minus the second one, or is it the other way around? – aevanko
the way you want to handle this cannot work (sumif ranges must be arrays of the same dimensions). Tell us how you are supposed to find the second cell so we could help you finding the right formula (with the tips Issun suggested) – JMax
Apparently you can pass Ranges of different sizes but in the end, excel extends the range to match the first range. Fun fact: office.microsoft.com/en-us/excel-help/…. So if he was to pass S1, it would be using S1:S3 I suppose? – aevanko

1 Answers

1
votes

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.