+ Reply to Thread
Results 1 to 17 of 17

Using sumifs where sum range's columns are non contiguous?

  1. #1
    Forum Contributor
    Join Date
    12-30-2012
    Location
    Israel
    MS-Off Ver
    2007, 2016
    Posts
    639

    Using sumifs where sum range's columns are non contiguous?

    Hi,
    I need to put non contiguous columns in the first parameter of the function SUMIFS (which is sum range). How do I do it?
    For example: =SUMIFS("A:A"&"C:C", SALES (named range), ">15", CATEGORY(named range),"=beverages")
    Last edited by GIS2013; 04-18-2016 at 07:19 AM.
    Please * if you like the answer

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

    Re: Using sumifs where sum range's columns are non contagious

    have you considered using pivot tables for this kind of work?
    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.

  3. #3
    Forum Contributor
    Join Date
    12-30-2012
    Location
    Israel
    MS-Off Ver
    2007, 2016
    Posts
    639

    Re: Using sumifs where sum range's columns are non contagious

    Yes, but the horizontal dimension of such a PT would be consisted of many kinds of discounts (each in a seperate column) per product, and so aggregate the discounts into 4-5 groups by calculated fields is not possible. Therefore I was thinking to use the flexibility of the sumifs function. It just that I don't know how to tell the first argument (sum range) to sum more than one column..
    Last edited by GIS2013; 04-18-2016 at 05:42 AM. Reason: Typo

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

    Re: Using sumifs where sum range's columns are non contagious

    1. It would help to see the workbook.

    You get better help on your question if you add a small excel file, without confidential information.

    Please also add manualy the expected result in your file.

    To Attach a File:

    1. Scroll down to the window below your post Additional Options
    2. In the frame Attach Files you will see the button Manage Attachments
    3. Click the button.
    4. A new window will open titled Manage Attachments - Excel Forum.
    5. Click the Browse... button to locate your file for uploading.
    6. This will open a new window File Upload.
    7. Once you have located the file to upload click the Open button. This window will close.
    8. You are now back in the Manage Attachments - Excel Forum window.
    9. Click the Upload button and wait until the file has uploaded.
    10. Close the window and then click Submit.

  5. #5
    Forum Contributor
    Join Date
    12-30-2012
    Location
    Israel
    MS-Off Ver
    2007, 2016
    Posts
    639

    Re: Using sumifs where sum range's columns are non contagious

    Please see attached file
    Attached Files Attached Files

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

    Re: Using sumifs where sum range's columns are non contagious

    with a helpcolumn to sum the data and after that a pivot table.

  7. #7
    Forum Contributor
    Join Date
    12-30-2012
    Location
    Israel
    MS-Off Ver
    2007, 2016
    Posts
    639

    Re: Using sumifs where sum range's columns are non contagious

    Thanks. And more generally - does the argument "sum range" in the sumifs function know how to handle non contiguous columns?

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

    Re: Using sumifs where sum range's columns are non contiguous?

    You could use 2 sumifs formula's also

  9. #9
    Forum Contributor
    Join Date
    12-30-2012
    Location
    Israel
    MS-Off Ver
    2007, 2016
    Posts
    639

    Re: Using sumifs where sum range's columns are non contiguous?

    You mean to add (+) 2 sumifs formula's?

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

    Re: Using sumifs where sum range's columns are non contiguous?

    Yep, that could also be a solution.

    But it is not an answer to your question. For that I will leave this question to another member.

  11. #11
    Forum Contributor
    Join Date
    12-30-2012
    Location
    Israel
    MS-Off Ver
    2007, 2016
    Posts
    639

    Re: Using sumifs where sum range's columns are non contiguous?

    Ok thanks oeldere

    The floor is open: does the argument "sum range" in the sumifs function know how to handle non contiguous columns?

  12. #12
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Using sumifs where sum range's columns are non contiguous?

    Try this

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  13. #13
    Forum Contributor
    Join Date
    12-30-2012
    Location
    Israel
    MS-Off Ver
    2007, 2016
    Posts
    639

    Re: Using sumifs where sum range's columns are non contiguous?

    Thanks Alkey, your solution works but since my real data is much bigger, I'd still wait for something more general.

  14. #14
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Using sumifs where sum range's columns are non contiguous?

    Quote Originally Posted by GIS2013 View Post
    Thanks Alkey, your solution works but since my real data is much bigger, I'd still wait for something more general.
    Not sure what you mean by "more general" but here is another way

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

  15. #15
    Forum Contributor
    Join Date
    12-30-2012
    Location
    Israel
    MS-Off Ver
    2007, 2016
    Posts
    639

    Re: Using sumifs where sum range's columns are non contiguous?

    Fantastic formula! By the way no need to press CTRL+SHIFT+ENTER. Why is that?

  16. #16
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    14,452

    Re: Using sumifs where sum range's columns are non contiguous?

    does the argument "sum range" in the sumifs function know how to handle non contiguous columns?
    My answer, although it could be wrong, is "no".

    Help file makes this observation: https://support.office.com/en-us/art...6-611cebce642b
    Quote Originally Posted by Excel Help
    Use the same number of rows and columns for range arguments.
    The Criteria_range argument must contain the same number of rows and columns as the Sum_range argument.
    So, even with a a contiguous 2D range, it will not work when you have 2 columns in the sum argument but only 1 column in the criteria arguments. Even if it does accept non-contiguous ranges like you are showing, it will also want 2D ranges for each criteria, meaning that it will want duplicate columns for each of your criteria. I would recommend solutions like those above (I would probably go with the =sumifs(...)+sumifs(...)
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  17. #17
    Forum Contributor
    Join Date
    12-30-2012
    Location
    Israel
    MS-Off Ver
    2007, 2016
    Posts
    639

    Re: Using sumifs where sum range's columns are non contiguous?

    I understand now . So I'll go with the multiple sumifs formula's.. and maybe naming my columns (using named ranges I mean) will make it easier to understand. Thx.

+ 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 with two columns in range
    By MrPablus in forum Excel General
    Replies: 3
    Last Post: 04-04-2016, 01:00 PM
  2. Replies: 3
    Last Post: 12-09-2015, 03:10 AM
  3. Can I use sumifs on a range with multiple columns?
    By JasperD in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-13-2014, 03:11 AM
  4. sumifs on a pivot table - compare range with range
    By pavlos in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-30-2013, 02:26 AM
  5. SUMIFS with multiple sum range columns
    By bvmk in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-11-2013, 03:39 PM
  6. [SOLVED] IF AND or SUMIFS comparing Months from 2 date columns and text from 2 other columns
    By jrochet in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 09-22-2012, 11:06 AM
  7. Writing a Procedure to print non-contagious area on the smae page
    By Basharat A. Javaid in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-05-2005, 09:05 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