+ Reply to Thread
Results 1 to 3 of 3

sumifs messes up sometimes while changing order of names - better formula please?

  1. #1
    Forum Contributor
    Join Date
    02-07-2013
    Location
    London
    MS-Off Ver
    Excel 2019
    Posts
    105

    sumifs messes up sometimes while changing order of names - better formula please?

    Hi guys,

    I hope you will find a solution to my problem, any help greatly appreciated. Workbook attached.
    I have a workbook where I record nursery fees weekly, and there's a formula that causes some trouble in payment columns
    1. column [L] should sum only card payments from "PP" worksheet
    2. column [M] should sum only cash payments
    3. column [N] should sum : vouch, chq and bacs payments

    it is set up like this using sumifs formula, but sometimes when I change children names order [clear cells with one child name and put it for example on 2 rows down, and the child from two rows down to the first child's cell, then payments made by the second child appear on first child's payments and other way round.

    Could anyone look into the file and advice what other formula I could use to make it better, or how to change the existing one so it works PLEASE?

    Maaaany thanks!
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    06-26-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010
    Posts
    1,673

    Re: sumifs messes up sometimes while changing order of names - better formula please?

    The formulas look okay. In fact, you state they are working until you modify the order of the children.

    How exactly are you doing that?

    Can you provide a spreadsheet with data (I assume you need more than two children) and then a step by step process (e.g. what cells you are highligting, are you hitting delete or cut, etc.?) to show the problem?
    Pauley
    --------
    If I helped with your issue, I'd appreciate a rep bump (hit the '*' icon to the bottom left of this post).

  3. #3
    Forum Contributor
    Join Date
    02-07-2013
    Location
    London
    MS-Off Ver
    Excel 2019
    Posts
    105

    Re: sumifs messes up sometimes while changing order of names - better formula please?

    Hi Pauleyb thanks for your fast reply.
    Yes the formulas seem to be okay, i have no idea what causes the problem, but when i redo and copy down it seems to solve the problem.
    So children are sorted alphabetically to match weekly managers reports. Sometimes I change the order, that's why I added one extra column, to match manager's order so it's faster to process. Sometimes when i change the order [by hitting delete on name/surname and entering other childs details, the formula still "thinks" it's the "old" child in there.. and summing his/her payments normally where it used to be. Sometimes I can do this action without any formulas issue but sometimes it can be a pain. It's hard to explain or show, I tried to swap the names as in the workbook attached.

    Becuase it's hard to give an example I hoped someone could come up with a better way of getting card/cash/vouch/chq/bacs payments from parents payments tab "PP"

    PP sheet has - sheet name, in column A and same time equals cell D1 of each sheet.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Changing order of names
    By lotusfixer in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-13-2012, 09:40 AM
  2. Replies: 1
    Last Post: 11-02-2012, 12:18 AM
  3. Changing order of names
    By charlie11k in forum Excel General
    Replies: 11
    Last Post: 07-21-2009, 10:18 AM
  4. Alphabetically order list messes up formulas ..
    By Marco-Kun in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-26-2009, 07:24 PM
  5. [SOLVED] Changing the order of names...
    By Mickie in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 05-23-2006, 07:45 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1