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

1. ## 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")  Register To Reply

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

have you considered using pivot tables for this kind of work?  Register To Reply

3. ## 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..  Register To Reply

4. ## 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.

To Attach a File:

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.
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.  Register To Reply

5. ## Re: Using sumifs where sum range's columns are non contagious  Register To Reply

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

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

7. ## 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?  Register To Reply

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

You could use 2 sumifs formula's also  Register To Reply

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

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

10. ## 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.  Register To Reply

11. ## 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?  Register To Reply

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

Try this

Formula:  `Please Login or Register  to view this content.`  Register To Reply

13. ## 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.  Register To Reply

14. ## Re: Using sumifs where sum range's columns are non contiguous? Originally Posted by GIS2013 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:  `Please Login or Register  to view this content.`  Register To Reply

15. ## 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?  Register To Reply

16. ## 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 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(...)  Register To Reply

17. ## 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.  Register To Reply