+ Reply to Thread
Results 1 to 5 of 5

Using SUMIF across multiple rows

  1. #1
    Registered User
    Join Date
    05-18-2011
    Location
    North Carolina
    MS-Off Ver
    Excel 2003
    Posts
    29

    Using SUMIF across multiple rows

    Hello. I am creating a spreadsheet that will add up the monthly totals of specified materials. ie For all projects that use material "C110", I want monthly totals of how many pounds of that material were used. I have attached a sample spreadsheet to further explain what I am trying to do. It's probably a simple formula, I'm just not sure how to get the SUMIF function to work across multiple rows. In reality, this spreadsheet will be much longer. Thanks for the help!
    Attached Files Attached Files
    Last edited by wvpersephone13; 05-21-2012 at 01:13 PM. Reason: Solved

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,613

    Re: Using SUMIF across multiple rows

    Put this in F10...

    =SUMIF($C$3:$C$9,$E15,F$4:F$10)

    ...and copy it down and across to Q19.

    Note; for your data configuration, it is necessary for the sum rows F$4:F$10 to be offset by one row from the criteria rows $C$3:$C$9.
    Last edited by AlphaFrog; 05-21-2012 at 10:22 AM.

  3. #3
    Registered User
    Join Date
    05-18-2011
    Location
    North Carolina
    MS-Off Ver
    Excel 2003
    Posts
    29

    Re: Using SUMIF across multiple rows

    Thank you AlphaFrog. What do you mean by your Note statement? When I copied this over to my larger spreadsheet, it appears to be adding all of column F, instead of just the alternating lines I want to add.

  4. #4
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,613

    Re: Using SUMIF across multiple rows

    I don't know how your Larger sheet is configured.

    On your Sample sheet, every two cell pairs in C3:C10 are merged. Only the first cell in the merged pairs has the value. As a test, put this in two empty cells =C3 and =C4. Only C3 returns a value.

    The rows in column F you want to Sum is one row below the matched row in column C. So if C3 is a match, then you want to sum from F4. That's why "it is necessary for the sum rows F$4:F$10 to be offset by one row from the criteria rows $C$3:$C$9."

    If your Larger sheet is configured differently, you may need to change the formula.

  5. #5
    Registered User
    Join Date
    05-18-2011
    Location
    North Carolina
    MS-Off Ver
    Excel 2003
    Posts
    29

    Re: Using SUMIF across multiple rows

    Ah, I understand now. This works perfectly, thank you!

+ 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.05785 seconds
  • Memory Usage 8,937KB
  • Queries Executed 15 (?)
More Information
Template Usage (32):
  • (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
  • (1)postbit_attachment
  • (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
  • ./packages/vbattach/attach.php
  • ./vb/types.php
  • ./vb/cache.php
  • ./vb/cache/db.php
  • ./vb/cache/observer/db.php
  • ./vb/cache/observer.php
  • ./includes/functions_notice.php 

Hooks Called (47):
  • 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_attachment
  • 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