+ Reply to Thread
Results 1 to 7 of 7

Split a Sumif

  1. #1
    Valued Forum Contributor
    Join Date
    11-15-2007
    Location
    England
    MS-Off Ver
    Office Pro 2003 / 2010
    Posts
    423

    Split a Sumif

    Hi, been a while since I visited, takes me back.

    I have a workbook with a worksheet called Download in it, once a month I download data and paste it into this sheet.

    There are then 12 sheets one for each month of the year Columns E6 downward on each sheet are Income.

    Column H in each sheet is used for a unique one letter code.

    At the end of the Workbook is an Income & Expenditure Sheet with all of the unique codes A - V in column A, a Description in Column B and then the Income for each month in columns C (Jan) - N (Dec).

    The layout is like this -

    Col Col Col
    A B C

    Code Description Jan

    A Rent =SUMIF(Jan!$H$6:$H$150,'Income & Expenditure'!$A7,Jan!$E$6:$E$150)
    B Water
    C Insurance

    For Jan the formula is =SUMIF(Jan!$H$6:$H$150,'Income & Expenditure'!$A7,Jan!$E$6:$E$150) so it sums all of the income for code A for example on the Jan sheet and shows it on the income and expenditure sheet in D7, Feb is in E7 and so on and coder B is in D8 for Jan and E8 for Feb etc.

    Is there a way to split the sumif?

    So say the income for Jan in a specific cell on the Jan Sheet was £100 and £50 should be allocated to code A and £50 to code B on the Income & Expenditure Sheet is there a workaround to split the value £100?

    Many thanks for any and all assistance.

  2. #2
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    Re: Split a Sumif

    How do you know that the value needs to be split out? Is there something on the worksheet that would indicate that, or is it something that you just know from eperience that some clerk has made a bad journal or ledger entry? The SumIf is based on a criteria defined in cell A7 on the Income & Expenditure worksheet. If one of the entries is incorrectly coded or has a wrong value, there is no way for the formula or vba to know that. I think human intervention is probably the best answer, unless there is something else in one of the worksheets that would flag the item as errant.
    Last edited by JLGWhiz; 06-01-2020 at 08:13 AM.
    Any code provided by me should be tested on a copy or a mock up of your original data before applying it to the original. Some events in VBA cannot be reversed with the undo facility in Excel. If your original post is satisfied, please mark the thread as "Solved". To upload a file, see the banner at top of this page.
    Just when I think I am smart, I learn something new!

  3. #3
    Valued Forum Contributor
    Join Date
    11-15-2007
    Location
    England
    MS-Off Ver
    Office Pro 2003 / 2010
    Posts
    423

    Re: Split a Sumif

    Thanks for replying JLGWhiz.

    We know that income can sometimes appear as a single entry on the download say £100, but that £100 sometimes covers more than one item,
    a person might pay for several things all in the same payment, so we want to be able to lookup the £100 and then split it rather than
    have it appear as a single item coded to one letter when it should be split between three.

  4. #4
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,198

    Re: Split a Sumif

    but back to Tim's question How do you know that the value needs to be split out?
    what does it need to be allocated to?
    Will payment always be in full?

  5. #5
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Split a Sumif

    HOW TO ATTACH YOUR SAMPLE WORKBOOK:

    Fast answers need clear examples. Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. Just before posting, scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  6. #6
    Valued Forum Contributor
    Join Date
    11-15-2007
    Location
    England
    MS-Off Ver
    Office Pro 2003 / 2010
    Posts
    423

    Re: Split a Sumif

    Hi davsth,

    Sorry must be the Monday morning things not registering in what's left of the grey matter. I see where you are coming from now and I
    don't see a way around that. I'll talk to my colleague and see if the download can be manipulated in any way.

    Thanks for the help everyone.

  7. #7
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Microsoft Office 2013
    Posts
    3,215

    Re: Split a Sumif

    Its better to attach sample file with desire output. Please see yellow banner on top of the screen.


    atul


    If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.

    Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".

+ 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. Replies: 3
    Last Post: 11-05-2019, 03:02 AM
  2. Need Macro code to split data split in 7 sheets based on variable rows in column A
    By Alija_21 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-31-2015, 08:03 AM
  3. Split cell to create sorted heading on empty row above and keep second split info
    By CastingDirector in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-09-2015, 12:24 AM
  4. SUMIF & Split Strings
    By JimmyT10 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 01-16-2015, 08:29 PM
  5. [SOLVED] Split Macro modification to Split into new Workbooks instead of sheets within one workbook
    By DLSmith in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-13-2012, 08:11 PM
  6. Can excels Split() function split a string up at multiple spots?
    By 111StepsAhead in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-14-2011, 02:36 PM
  7. Sumif formula needs to split 2 criterias of addition
    By 3smees23 in forum Excel General
    Replies: 4
    Last Post: 08-07-2009, 03:45 AM

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