+ Reply to Thread
Results 1 to 5 of 5

Array SUM Formula for SUM with multiple criteria and a date range (Using Excel 2003)

  1. #1
    Registered User
    Join Date
    02-18-2004
    Posts
    71

    Array SUM Formula for SUM with multiple criteria and a date range (Using Excel 2003)

    I have a list of timesheet data for various employees where I am trying to sum the number of hours worked for a particular Sunday week commencing date by employee. I had this working when it was looking at one month in isolation but I have been asked to change this so that when the week covers two months that the “this weeks” summary of hours worked still takes into account the full 7 days not just the part week that applies to the current month

    Eg My data is structure like this

    Employee Code = an alpha code that is unique per employee
    Charge Flag = can be Y or N
    Hours Worked = number of hours from the timesheet
    Doc_MM = the month number that the timesheet relates to ie 4 = April
    Doc_DD = the day number that the timesheet relates to

    So for week commencing Sunday the 27th April 2008 I need to sum the number of hours worked by employee for timesheets with a chargeable flag = Y for the following doc_MM and doc_dd combinations

    Doc_DD Doc_MM
    27 4
    28 4
    29 4
    30 4
    1 5
    2 5
    3 5

    Remembering that the raw data for this example would contain all the timesheets for the month of April and May.

    This spreadsheet is updated by a user each week and the focus W/C date will change each week

    Any help would be greatly appreciate

    Thanks

    Karen

  2. #2
    Forum Contributor
    Join Date
    01-05-2004
    Location
    Helsinki, Finland
    Posts
    100
    Hi,

    why don't you make an extra column Doc_WK where you have the weeknumber of the date (using WEEKNUM-function) and summarize the values by that column?

    - Asser

  3. #3
    Registered User
    Join Date
    05-15-2008
    Posts
    9

    Conditional sum array

    Below is an array I use to sum data from diffferent tabs using variance criteria. I hope this helps.


    =SUM(IF(('(7.2b) Raw Lawson Data'!$A$6:$A$6000=9291)*('(7.2b) Raw Lawson Data'!$E$6:$E$6000=$A14),'(7.2b) Raw Lawson Data'!$D$6:$D$6000,0))+SUM(IF(('(7.2b) Raw Lawson Data'!$A$6:$A$6000=9211)*('(7.2b) Raw Lawson Data'!$E$6:$E$6000=$A14),'(7.2b) Raw Lawson Data'!$D$6:$D$6000,0))

  4. #4
    Registered User
    Join Date
    02-18-2004
    Posts
    71
    Hi Asser

    The weeknum function will probably cause me issue in the Dec/Jan cross over period.

    However thank-you for your post as your suggestion gave me the idea to use vlookup to just add some text next to the dates for that week and use that as one of the criteria

    Sometimes you just need a new set of eye's to look at something

    Cheers

    Karen

  5. #5
    Forum Contributor
    Join Date
    01-05-2004
    Location
    Helsinki, Finland
    Posts
    100
    Hi Karen,

    I'm glad I could give you a kick in a new direction If you need any help in summarizing the data, just post in this thread and I'll try to help you as I can.

    - Asser

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

X vBulletin 4.1.8 Debug Information

  • Page Generation 0.05925 seconds
  • Memory Usage 8,908KB
  • Queries Executed 14 (?)
More Information
Template Usage (31):
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_global_above_footer
  • (1)ad_global_below_navbar
  • (1)ad_global_header1
  • (1)ad_global_header2
  • (1)ad_navbar_below
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (1)ad_thread_first_post_content
  • (1)ad_thread_last_post_content
  • (1)footer
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)headinclude_bottom
  • (5)memberaction_dropdown
  • (1)navbar
  • (4)navbar_link
  • (4)navbar_noticebit
  • (6)option
  • (5)postbit_legacy
  • (5)postbit_onlinestatus
  • (5)postbit_wrapper
  • (4)showthread_bookmarksite
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper
  • (1)vbseo_linkbackmenu_entry 

Phrase Groups Available (6):
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files (39):
  • ./vbseo.php
  • ./env.php
  • ./vbseo/includes/functions_vbseo.php
  • ./vbseo/includes/functions_vbseo_pre.php
  • ./vbseo/includes/functions_vbseo_url.php
  • ./vbseo/includes/functions_vbseo_createurl.php
  • ./vbseo/includes/functions_vbseo_db.php
  • ./vbseo/includes/functions_vbseo_vb.php
  • ./vbseo/includes/functions_vbseo_seo.php
  • ./vbseo/includes/functions_vbseo_misc.php
  • ./vbseo/includes/functions_vbseo_crr.php
  • ./vbseo/includes/functions_vbseo_cache.php
  • ./vbseo/includes/functions_vbseo_hook.php
  • ./vbseo/includes/functions_vbseo_startup.php
  • ./includes/config.php
  • ./showthread.php
  • ./global.php
  • ./includes/class_bootstrap.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/functions.php
  • ./includes/class_friendly_url.php
  • ./includes/class_hook.php
  • ./includes/functions_cforum.php
  • ./includes/functions_facebook.php
  • ./includes/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/class_bootstrap_framework.php
  • ./vb/vb.php
  • ./vb/phrase.php
  • ./includes/functions_notice.php
  • ./packages/vbattach/attach.php
  • ./vb/types.php
  • ./vb/cache.php
  • ./vb/cache/db.php
  • ./vb/cache/observer/db.php
  • ./vb/cache/observer.php 

Hooks Called (46):
  • init_startup
  • friendlyurl_resolve_class
  • database_pre_fetch_array
  • database_post_fetch_array
  • global_bootstrap_init_start
  • global_bootstrap_init_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • global_state_check
  • global_bootstrap_complete
  • global_start
  • style_fetch
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • friendlyurl_redirect_canonical
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • fetch_musername
  • cache_templates
  • template_register_var
  • parse_templates
  • notices_check_start
  • notices_noticebit
  • process_templates_complete
  • reputation_image
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_display_complete
  • memberaction_dropdown
  • tag_fetchbit_complete
  • forumrules
  • showthread_bookmarkbit
  • navbits
  • navbits_complete
  • showthread_complete


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1