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

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

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

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.

Please see attached file

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

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

You could use 2 sumifs formula's also

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

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.

Ok thanks oeldere

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

Try this

Formula:

Please Login or Register  to view this content.

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

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.

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

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

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.