CODEWITHSUNDEEP
excelexcel-formulasumifs
Haleem Syed
Haleem Syed

Reputation: 25

Multiple Sumrange in SUMIFS excel

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.

Upvotes: 1

Views: 14513

Answers (3)

LJ01
LJ01

Reputation: 589

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

Upvotes: 0

user4039065
user4039065

Reputation:

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)

Upvotes: 1

ashleedawg
ashleedawg

Reputation: 21657

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:

Upvotes: 0

Related Questions