concatenating ranges from different sheets not working with sumif in google sheets

Question

background

I'm writing a script in a sheet that relies on ranges from a different sheet. For example this line works perfectly fine using SUMIF:

=sumif(accounting!$D$3:$D$200,A2, accounting!$C$3:$C$200)

problem

when i try to add ranges that consist of subranges (as per instructions here):

You can join multiple ranges into one continuous range using this same punctuation. For example, to combine values from A1-A10 with the values from D1-D10, you can use the following formula to create a range in a continuous column: ={A1:A10; D1:D10}

like so:

=sumif({accounting!D3:D6;accounting!D10:D16;accounting!D27:D28},
        A2, 
       {accounting!C3:C6;accounting!C10:C16;accounting!C27:C28})

it gives me the following error

argument must be a range

note

keep in mind this works

=sumif({accounting!D3:D6;accounting!D10:D16;accounting!D27:D28},
        A2,
        accounting!C3:C6)

it seems only when i add a range to the third arguments do things blow up.. ideas?

Answer 1

the third argument of sumif is a range by default settings of this formula. There is no way to use it with an array.

You may use sum + filter

=round(sum(filter({accounting!C3:C6;accounting!C10:C16;accounting!C27:C28},
{accounting!D3:D6;accounting!D10:D16;accounting!D27:D28}=A2)), 1) 

Not sure about Excel, this works only in Google Spreadsheets. Here's sample sheet