Multiple Sumrange in SUMIFS excel

1
votes

I am trying to use SUMIFS in excel. I was thinking I can select the multiple cell for one sumrange criteria (SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)) in different column by pressing Ctrl+select but its not the case. When I am selecting like this, its adding comma and going to another criteria. I can able to achieve this by adding multiple sumifs like =sumifs() + sumifs () to achieve what I need. I wrote the working formula as

=SUMIFS('Daily Log Sheet'!E3:E63, 'Daily Log Sheet'!B3:B63, 'Monthly Record'!B4) + SUMIFS('Daily Log Sheet'!E74:E144, 'Daily Log Sheet'!B74:B144, 'Monthly Record'!B4)

Instead of using multiple sumifs statement, is there any way to select multiple cell in different column in one criteria. Like by adding & operator in one criteria. I tried this but not working.

Its taking a lot of time in writing multiple sumifs statement and I have many sheets to work on. There is any way to select multiple cell in one criteria.

3
excelexcel-formulasumifs
Could you use sumifs and filter out the ranges that you want to skip using a second condition? – LJ01
Sorry I edited the sumifs syntax. I am using sumifs only but its not working. I want to use muliple sum_range in one sumifs. – Haleem Syed
Can you explain why you want to use multiple sum range in the same formula? There might be a better way – LJ01
@LJ01, I have to omit some cells in between the coloum. – Haleem Syed

3 Answers

1
votes

SUMPRODUCT will allow you to exclude rows 64:73.

=SUMPRODUCT(('Daily Log Sheet'!B3:B144='Monthly Record'!B4)*((ROW($3:$144)<64)+(ROW($3:$144)>73)), 'Daily Log Sheet'!E3:E144)
0
votes

The syntax of the SUMIF and SUMIFS Functions are:

SUMIF(range, criteria, [sum_range])

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

... regardless of whether it's being used in an array formula.

The number of parameters in a function do not change just because it's being used in an array formula.

Parameters shown in [ square brackets ] are optional. If there's more than one criterion within the same set of [ square brackets ] — like [criteria_range2, criteria2] — then they all must be included or excluded together.

Outside of that, you can't arbitrarily add or remove parameters from functions.

More information:

0
votes

I have never tried this, and can't test it on my phone but you might be able to use a standard sum() function, and an if as an array formula....

=Sum(if({criteria_range1}={criteria1},{sum range1},if{criteria_range2}={criteria2},{sum range 2})