Can you help me calculate a total in my spreadsheet?

Henriette van Graan March 25, 2013

My spreadsheet has 3 columns and 20 rows:

  • Column 1 = ODO meter readings (distance travelled from point A to point B).
  • Column 2 = Private use.
  • Column 3 = Business use.

I want to total Column 2 as follows: IF column 2 = Private use, it must add the ODO meter reading in column 1 or IF column 3 = Business use, it must add the ODO meter reading in column 1.

I would like to attach the spreadsheet in order for you to get a better understanding, but it seems it is not possible at this stage.

  1. Alan Wade
    March 26, 2013 at 8:02 am

    Well I typed out a reply but guess what? It went missing!

    Rather than type it all out again add a column after the ODO column which I will assume is A1.
    Call the column Code.
    In the cells in this column use P for Private and B for Business
    In the next cell (Private) enter this code: =IF(AND(B2="P");A2;"")
    In the last cell (Business enter this code: =IF(AND(B2="B");A2;"")

    If you dont understand let me know and I will type it out in a more detailed fasion assuming it dosnt disappear again.

    • Chris Marcoe
      March 27, 2013 at 5:41 pm

      It's there. Might have just gotten eaten for a while and then spit back out.

  2. Alan Wade
    March 26, 2013 at 7:56 am

    Here is another way to do this.
    Assuming A1 = ODO B1 = Code C1 = Private and D1 = Business (Yes, I have added the Code column).

    Decide which code you want to use for example P and B which is obvious or 1 and 2 etc
    In cell C2 add this code: =IF(AND(B2="P");A2;"")
    In cell D2 add this code: =IF(AND(B2="B");A2;"")

    If you want to use numbers like 1 and 2 for the code dont wrap it in "" as I have done with the P and B.

    Now your *Private* cell will check to see what code you have entered and if it matches then will copy the data from the ODO cell as will your business cell.
    If you use the letters P and B (which makes sense to me) then the letters are not case sensitive.

  3. Bruce Epper
    March 26, 2013 at 2:33 am

    If you are using Excel,you can use the following formula to calculate personal use (assuming headers in row 1 and 20 rows of data): =SUMIF(B2:B21,"Personal",A2:A21). To do the business use, it would change to: =SUMIF(C2:C21,"Business",A2:A21). These formulas also assume that the respective column actually contains the word "Personal" in B or "Business" in C when each applies. Because of this, it would be easier if you just used column B with the appropriate word ("Personal" or "Business") instead of using 2 separate columns. In that case the formulas would change to: =SUMIF(B2:B21,"Personal",A2:A21) and =SUMIF(B2:B21,"Business",A2:A21) respectively.