SUMIF with multi-area selection

TomCon

Active Member
Joined
Mar 31, 2011
Messages
373
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
This does not seem to work

=SUMIF((G417:G419,G422:G425),">0")

Produces #VALUE. In the first argument, I am trying to give a multi-area range.

When using the UI, if you just make a multi-area selection, you will get an error in the formula and it will not even be accepted.

If you hand-edit you can type in the above formula, but it gives #VALUE as the result.

I had thought that if you had a multi-area selection for a function argumet, and enclosed it in (), that it would be treated as a single argument to the function. There are some functions for which this seems to be true. But, for SUMIF it appears to not be true.

So, any way to get a multi-area selection with SUMIF?

Thanks!
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Use the SUMIFS function:

=SUMIFS(sum_range,criteria_range_1,criteria_1,criteria_range_2,criteria_2, ...)
 
Upvote 0
I had thought that if you had a multi-area selection for a function argumet, and enclosed it in (), that it would be treated as a single argument to the function. There are some functions for which this seems to be true. But, for SUMIF it appears to not be true.

You are correct - SUMIF will not accept range unions. FREQUENCY will, however:

=INDEX(FREQUENCY((G417:G419,G422:G425),0),2)

though this only works here since your criterion (">0") happens to be a numerical construction. Otherwise, the solutions become a little more convoluted.

Regards
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top