SumIf for a range not working

[magnethero]

Should be simple but I can't make it work.

I have a tab with a large amount of data for 100 departments (all data relative on individual lines)

In another tab I want to create a summary of several departments for a given column.

Formula as it is now looks like:

=SUMIF('Tab1'!A:A,'Tab2'!A1:A20,'Tab1'!N:N)

Why would this not pick up the 20 values of column N with respect to their appearance in column A? How do I make this work?

Thanks!

 

[crazydragon84]

=sum(SUMIF('Tab1'!A:A,'Tab2'!A1:A20,'Tab1'!N:N)) then make this an array formula by ctrl+shift+enter

 

[magnethero]

AWESOME!!! Got it to work! But I don't really understand why turning it to an array solved it with the additional SUM function. Could you explain? Maybe there is also a thread that better explains how an array functions?

 

[crazydragon84]

sumif can't handle multiple values to add (your second criteria). Array formula is basically breaking out the sumif into 20 different parts in this case. essentially its doing Sum(SUMIF('Tab1'!A:A,'Tab2'!A1,'Tab1'!N:N), SUMIF('Tab1'!A:A,'Tab2'!A2,'Tab1'!N:N), SUMIF('Tab1'!A:A,'Tab2'!A3,'Tab1'!N:N), ...... SUMIF('Tab1'!A:A,'Tab2'!A20,'Tab1'!N:N))

 

[magnethero]

Okay that makes sense! Thank you for the explanation! Love learning new things for Excel ^^

 

[crazydragon84]

np glad i was able to help