+ Reply to Thread
Results 1 to 6 of 6

Amalgamate Multiple SUMIFS Statements Into One

  1. #1
    Registered User
    Join Date
    10-04-2012
    Location
    Not here, it's rubbish!
    MS-Off Ver
    Excel 2003/2007/2010
    Posts
    66

    Amalgamate Multiple SUMIFS Statements Into One

    Hi All,

    The problem in question is shown in the sample file attached. For the dates in September 2014, tasks were undertaken on certain days including weekends (highlighted) and the time spent on the task is recorded in column D. Column B is a sime TEXT function that identifys the day of the week against a particular date.

    I need to sum all the times for weekend days. I could do this using two SUMIF statements to identify Saturday and Sunday and add them together [SUMIF(B3:D32,"Saturday",D3:D32)+SUMIF(B3:D32,"Sunday",D3:D32)] shown in cell H4.

    However, when it comes to summing times for weekdays I'd need five SUMIFS to identify Monday, Tuesday, etc and then add them up which is getting cumbersome.

    I'm wondering if anyone knows if it's possible to amalgamate multiple SUMIF statements into one single cleaner statement that will do the same job for weekend days and weekdays or if there is an alternative function that will achieve this.

    Many thanks
    Attached Files Attached Files

  2. #2
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    8,277

    Re: Amalgamate Multiple SUMIFS Statements Into One

    =SUM(SUMIF(B3:D32,{"Saturday";"Sunday"},D3:D32))
    Try this
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  3. #3
    Registered User
    Join Date
    10-04-2012
    Location
    Not here, it's rubbish!
    MS-Off Ver
    Excel 2003/2007/2010
    Posts
    66

    Re: Amalgamate Multiple SUMIFS Statements Into One

    Fantastic! Works a treat!

    Thank you so much!

  4. #4
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    8,277

    Re: Amalgamate Multiple SUMIFS Statements Into One

    You are welcome and thanks for your feedback

  5. #5
    Forum Expert boopathiraja's Avatar
    Join Date
    07-12-2013
    Location
    Coimbatore,TamilNadu, India
    MS-Off Ver
    Excel 2007, 2013, 2016, 365
    Posts
    1,455

    Re: Amalgamate Multiple SUMIFS Statements Into One

    hi, TRY

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Click just below left if it helps, Booțathì

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    ”Soft Office 365. Learning Excel all over again!!
    Posts
    30,871

    Re: Amalgamate Multiple SUMIFS Statements Into One

    For weekends use this:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    For weekdays, use this:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Glenn



+ 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. SUMIFS Puzzle - Trying to avoid adding multiple SUMIFS to get valid result
    By haldavid in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-09-2013, 03:42 PM
  2. [SOLVED] How to amalgamate multiple worksheet_change events on one sheet. Coding help!!
    By leggy59 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-04-2012, 11:33 AM
  3. Excel 2007 : Multiply If statements and Sumifs
    By Batman11692003 in forum Excel General
    Replies: 0
    Last Post: 10-16-2011, 09:18 PM
  4. IF statements:SUMIF, SUMIFS and Ranges
    By gaznev2000 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-21-2010, 02:00 AM
  5. Sumifs Questions - Multiple Statements
    By WorkMan in forum Excel General
    Replies: 2
    Last Post: 11-26-2009, 08:07 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