+ Reply to Thread
Results 1 to 4 of 4

Sub Total Data based on Criteria

  1. #1
    Registered User
    Join Date
    03-25-2015
    Location
    USA
    MS-Off Ver
    2010
    Posts
    34

    Question Sub Total Data based on Criteria

    I need some help trying to figure out how to do the following:

    I have coded some vba script and havent figured out the most efficient way of getting a solution. The code seems to be partial working but instead of subtotaling each block of data it keeps the previous balance and adds it t the next block.

    There are two tables in my sheet,
    -Table1 Represents days in which hours were present with 1's.
    -Table 2 is the actual Hours logged by the individual.

    My goal is to apply a dollar amount only once per day following the next criteria:
    1) Subtotal each day data block in Table 1 (Having Issues with, i dont seem to be understanding the examples that lead to my code)


    2) once each block is subtotaled, if the number is greater than 1, i know there is more than one time entry on that day. i would use the second table to determine which day item logged the most hours. The item with the most hour would remain while the other 1s on that day would be zeroed out. (not yet coded)

    this would allow to only apply the dollar amount once per day to the biggest time entry event.

    Im attaching my test file for reference.

    Any help in getting the code to work or implementing a different solution would be appreciated. This needs to be in vba as i will further add more parts for a automated process.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    11-02-2016
    Location
    NY
    MS-Off Ver
    2010
    Posts
    459

    Re: Sub Total Data based on Criteria

    Ok at first glance, i was going to put the info on different tabs (one for Count, another for Hours, another for for the Dollar amount). Changed my mind, use a Pivot table for DATA only. See format in attached file. If you need dollars, you can add another column OR have a lookup table (I'm assuming on the Employee id/ (IdCard)) to put in dollars. This solution does NOT require VBA. But you may want to investigate pivot tables.

    Good luck...let me know if this helps
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    03-25-2015
    Location
    USA
    MS-Off Ver
    2010
    Posts
    34

    Re: Sub Total Data based on Criteria

    Thakyou queuesef! This helps but still interested in solving the VBA solution as one of the driving factors behind using vba is to reduce the probability of human error with manual changes as I am not the intended recipient. The intended person has been known to deviate from the process which is what causes most of my cleanup issues.

  4. #4
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: Sub Total Data based on Criteria

    Not really sure if you need this solution or not as your thread is marked solved. Well anyway here it goes:

    Please Login or Register  to view this content.
    Your problem was that as you filled the empty cell with the sum value you extended the range with values so this is why it keeps the old value as well as adding the new values. In the macro I wrote it checks the empty values in column A, a column that that is not filled by the macro.

    As the result may be difficult to distinguish from the other values I would suggest making a change to the macro i.e. add a line that color result cells green.

    Please Login or Register  to view this content.
    after the "Cells(i, j) = sumit" line.


    Alf
    Last edited by Alf; 12-19-2018 at 12:35 PM.

+ 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. [SOLVED] Sum Total Based On Four Different Sets of Criteria
    By garrett.grillo in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 07-12-2017, 04:16 PM
  2. [SOLVED] Extract value or sum total based on month criteria that falls within Data Range
    By Mysore in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-02-2013, 11:31 PM
  3. [SOLVED] Sum based on criteria and show total only on row that has criteria
    By NU2vba in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-23-2012, 03:59 PM
  4. Get a sum total based on three criteria
    By Weasel in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 01-12-2009, 02:33 PM
  5. Total based on two criteria
    By mybuttonisbroke in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 07-31-2008, 01:18 PM
  6. how to total based upon criteria
    By mkmed in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 07-22-2008, 07:38 AM
  7. get a total based on criteria in two columns
    By rar in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-22-2005, 10:30 AM

Tags for this Thread

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