# 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")

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

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

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

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.

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.

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?

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

You could use 2 sumifs formula's also

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

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

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.

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?

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

Try this

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

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.

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.`

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?

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(...)

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.

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

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