1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Working w/totals in sorted/subtotaled spreadsheet

Discussion in 'Business Applications' started by BratDawg, Feb 4, 2013.

Thread Status:
Not open for further replies.
Advertisement
  1. BratDawg

    BratDawg Thread Starter

    Joined:
    Sep 23, 2004
    Messages:
    674
    First Name:
    Carla
    I have a spreadsheet where I have sorted and created subtotals. I want to roll up the totals so I only see the name and total then I want to work with those totals. Usually I'm trying to figure out the difference between what my accounting software is showing and a vendor invoice or something like that. So I'm cobbling information together and then trying to run a difference column.

    If I'm working with a rolled up spreadsheet like this, tho, and I copy a formula down in the difference column (like column b - c) I get bad numbers because it's not just looking at my subtotal lines, it's looking at ALL the lines.

    I tried copying and pasting values into another worksheet but that didn't seem to work either. Suggestions?

    Attaching a sample so you can see what I'm talking about.

    Thanks for any suggestions. Our accounting software is designed so we have to rely extensively on excel exports for most reconciliation type tasks. :(
     

    Attached Files:

  2. draceplace

    draceplace

    Joined:
    Jun 8, 2001
    Messages:
    2,583
    Not sure if this what your looking for but something like this might work.
    =IF(ISBLANK(D1),SUM(B1,-E1),"0")

    Saying if D1 is blank then SUM B1 and negative E1...Else its 0.
    You can pull this formula down and it will sum lines where the deduction type is blank.
    I did a SUM on D4 in the example.
     
  3. BratDawg

    BratDawg Thread Starter

    Joined:
    Sep 23, 2004
    Messages:
    674
    First Name:
    Carla
    Thanks but when I tried to download it again to see what you did, it wouldn't open anything and said the file was corrupt. I tried pasting that formula in column g and got funny results.

    What I want to do is take raw export, sort and subtotal - in this case column B exported from my software. I then got an export of an AFLAC invoice and sorted by name also and plugged those totals in E. Then I wanted to subtract E from B to determine the difference between what was withheld and remitted from employee checks to what was invoiced by the insurance company. I only wanted to focus on the totals, not the individual transactions (expanded).

    I ended up, to get what I needed, copying the formula in F4 and pasting it in column F on each total line, as opposed to just copying down the formula. I'm trying to figure out a faster way. We have 140 employees and alot of them have aflac. :(
     
  4. draceplace

    draceplace

    Joined:
    Jun 8, 2001
    Messages:
    2,583
    I didn't update your workbook but I was able to pull it down and edit with no problems.
    When I copied the formula from TSG and pasted it made merged cells, which screwed things up. Maybe if you copy form a 'Code' box it will paste clean? If not simply got to 'format cell' - alignment and click the merge cells box till its empty.

    Code:
    =IF(ISBLANK(D1),SUM(B1,-E1),"0")
    This formula will allow you to just pull down instead of pasting each time. You will be able to focus on the difference because it will display a "0" if D1 is blank. You could change the "0" to anything you want.
     
  5. draceplace

    draceplace

    Joined:
    Jun 8, 2001
    Messages:
    2,583
    Strange but true. If I do "CNTL V" or Paste it makes a merged cell(When copied from TSG thread). Paste special "match desitnation format" paste correctly.
     
  6. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1088180

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice