SUMIFS Multiple records same column

[helmerr]

Perhaps someone can point me in the right direction:

I have a sheet that contains multiple transactions from the bank, which I import from fixed length txt.
I added names to each range to make it easier for entering formulas, etc.
I need to get the sum of each AMOUNT column that contains records from any part of the range FULLNAME that is also from DEPT 908 and ACCT is 252.
But, I also need to be able to get multiple records added together and set what FULLNAME values. (Multiple records, same column)

hope that makes sense. Currently I am using SUMIFS and can pull only one persons FULLNAME. When I add another, the second name never adds..

Thanks

=SUMIFS(AMOUNT,FULLNAME,{"CARDHOLDER1","CARDHOLDER2"},DEPT,908,ACCT,252)

 

[barry houdini]

Hello helmerr,

welcome to MrExcel

When you use an "array constant" like that in SUMIFS the result is also an array of values....so you need SUM to sum that array like this

=SUM(SUMIFS(AMOUNT,FULLNAME,{"CARDHOLDER1","CARDHOLDER2"},DEPT,908,ACCT,252))

 

[liveinhope]

Hi Barry how do you / can you make this work with more than one array constant .

The first formula im my screen shot below =SUM(SUMIFS(D:D,B:B,{"north","south"},C:C,"accts")) returns the expected result 107

The second formula =SUM(SUMIFS(D:D,B:B,{"north","south"},C:C,{"accts","IT"})) I am trying to show scores in D for anyone who is in either branch north or south and dept is either accts or IT (ie 4 possible combinations)
result 82 is the sum of scores for employees who are in both branch north and dept accts and the employees who are in both branch south and dept IT


Is there a way to do the calc I am after ?

thanks


[TABLE="width: 752"]
<TBODY>[TR]
[TD]name</SPAN>[/TD]
[TD]branch</SPAN>[/TD]
[TD]dept</SPAN>[/TD]
[TD]score</SPAN>[/TD]
[TD]SUMIFS</SPAN>[/TD]
[TD]SUMIFS FORMULA</SPAN>[/TD]
[/TR]
[TR]
[TD]al</SPAN>[/TD]
[TD]north</SPAN>[/TD]
[TD]sales</SPAN>[/TD]
[TD="align: right"]59</SPAN>[/TD]
[TD="align: right"]107</SPAN>[/TD]
[TD]=SUM(SUMIFS(D:D,B:B,{"north","south"},C:C,"accts"))</SPAN>[/TD]
[/TR]
[TR]
[TD]beth</SPAN>[/TD]
[TD]south</SPAN>[/TD]
[TD]IT</SPAN>[/TD]
[TD="align: right"]13</SPAN>[/TD]
[TD="align: right"]82</SPAN>[/TD]
[TD]=SUM(SUMIFS(D:D,B:B,{"north","south"},C:C,{"accts","IT"}))</SPAN>[/TD]
[/TR]
[TR]
[TD]col</SPAN>[/TD]
[TD]east</SPAN>[/TD]
[TD]accts</SPAN>[/TD]
[TD="align: right"]51</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]deb</SPAN>[/TD]
[TD]west</SPAN>[/TD]
[TD]IT</SPAN>[/TD]
[TD="align: right"]42</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ed</SPAN>[/TD]
[TD]north</SPAN>[/TD]
[TD]accts</SPAN>[/TD]
[TD="align: right"]34</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]fran</SPAN>[/TD]
[TD]south</SPAN>[/TD]
[TD]sales</SPAN>[/TD]
[TD="align: right"]93</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]greg</SPAN>[/TD]
[TD]east</SPAN>[/TD]
[TD]sales</SPAN>[/TD]
[TD="align: right"]30</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]hanna</SPAN>[/TD]
[TD]west</SPAN>[/TD]
[TD]accts</SPAN>[/TD]
[TD="align: right"]25</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY><COLGROUP><COL span=4><COL><COL></COLGROUP>[/TABLE]

 

[barry houdini]

You need to separate one of the "array constants" with commas and one with semi-colons like this

=SUM(SUMIFS(D:D,B:B,{"north","south"},C:C,{"accts";"IT"}))

Note that you can't use 3 of those in one SUMIFS formula

 

[helmerr]

Hello helmerr,

welcome to MrExcel

When you use an "array constant" like that in SUMIFS the result is also an array of values....so you need SUM to sum that array like this

=SUM(SUMIFS(AMOUNT,FULLNAME,{"CARDHOLDER1","CARDHOLDER2"},DEPT,908,ACCT,252))

Perfect, Thanks!