0

So lets say I have 2 columns, A e B, like for example:

        A        B         C  
    -----------------------------
1   |        |   A    |    B    |
    -----------------------------
2   |        |   4    |    19   |
    -----------------------------
3   |        |   4    |    26   |
    -----------------------------
    |   .... and so on ....     |
    -----------------------------
#   | Total  |       37         |
    -----------------------------

So basically I want to subtract every cell (A-B) and add every columns.

The math expression would be (C2-B2)+(C3-B3)+...+(n#-(n-1)#) Can anyone explain how it is done? Thanks in advance

--- Update ---

So now I have to input letters in middle and that row won't count, like:

      A        B        C
  ----------------------------
1 |        |   A    |   B    |
  ----------------------------
2 |        |   4    |   19   |
  ----------------------------
3 |        |   4    |   26   |
  ----------------------------
4 |        |      N.A.       |
  ----------------------------
5 |        |    5   |   21   |
  ----------------------------
6 |   .... and so on ....    |
  ----------------------------
# | Total  |        53       |
  ----------------------------

So now the row number 4 wont count but all other will.

2 Answers 2

2

Use SUMPRODUCT()

=SUMPRODUCT(C2:C14-B2:B14)

This will subtract each row and sum the resultants for rows 2 through 14. If you want something more dynamic, something that will grow or shrink with your table, You can use INDEX/MATCH to locate the row with Total in column A and set the row above as the ending row:

=SUMPRODUCT(C2:INDEX(C:C,MATCH("Total",A:A,0)-1)-B2:INDEX(B:B,MATCH("Total",A:A,0)-1)

This will now find "Total" in Column A and set the row above that as the ending point of the SUMPRODUCT iterations.

1
  • That worked but now I have a problem. Imagine I have to input "letters" in the middle. I'm going to edit the question to be be more explicit. Feb 9, 2018 at 8:43
1

The math expression (C2-B2)+(C3-B3)+...+(n#-(n-1)#) in the question is equivalent to (C2+C3+...+Cn)-(B2+B3+...+Bn), which can be further simplified to the Excel formula =SUM(C2:Cn)-SUM(B2:Bn) where n is the last row number.

By using the =SUM() formula, strings such as "N.A." are ignored (but bare in mind that Excel built-in error values such as "#N/A" will result in an error anyway).

5
  • And what if I use strings like "Not Arrived" or "Incoming"? Isn't there like a formula to sum every single cell but that one? Feb 9, 2018 at 16:36
  • @BrunoGibellino All strings including "Not Arrived" and "Incoming" are just strings, and =SUM() will simply ignore all strings. You can try to do an experiment to SUM cells containing a mix of numbers and strings.
    – Kenneth L
    Feb 12, 2018 at 3:32
  • Well I've already tried and it's output is #VALUE! That's why I'm asking if there is no other solution. Feb 12, 2018 at 8:02
  • 1. Is there any cell containing #VALUE or another error? 2. What exactly the "strings" are? I tested it in my Excel and did not experience the error.
    – Kenneth L
    Feb 14, 2018 at 6:51
  • I don't have any cell containing #VALUE only one currently containing Arrived. And when I delete it the total goes back to normal counting. It's hard to tell the exact strings I use because it's in constant update and sometimes I have to use other strings that usually I don't use. Feb 15, 2018 at 9:42

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .