sumif as array formula

[Tim Green]

Hi all,

I have a list of related accounts in column P, and the sales budgets on
Sheet1. I have tried the following formula to get the total sales budget for
all of the related accounts as follows:

=SUMIF(Sheet1!S:S,Sales!P450,Sheet1!E:E)

entered as an array formula. This does not work, but I hope from this that
it is clear what I was trying to do.

How can this be achieved?

Thanks,

Tim

 

[Peo Sjoblom]

Try

=SUMPRODUCT(--(ISNUMBER(MATCH(Sheet1!S1:S100,Sales!P450,0))),Sheet1!E1:E100)


note that you cannot use S:S or E:E you need to specify it (unless you are
using Excel 2007)


--


Regards,


Peo Sjoblom

 

[Luke M]

I think a SUMPRODUCT function might be what you are looking for.

http://www.xldynamic.com/source/xld.SUMPRODUCT.html#new

 

[Tim Green]

Thanks, this got me on the right track. For the information of anyone
searching for a method of using sumif with more than one value, this is my
final formula:

=SUMPRODUCT(--(VALUE('Sales
budgets'!$S$6:$S$33)=TRANSPOSE(VALUE(Sales!$P4:$P50)))*'Sales
budgets'!$E$6:$E$33)

entered as an array formula.

This compares the values in column S of the "sales budgets" sheet with all
of the values in column P of "Sales" and returns the corresponding values in
column E of sales budgets. In this case columns S and P contain account
numbers, and column E is the budget figure.

 

[Peo Sjoblom]

It will slow down the process and you don't need the unary minuses nor do
you need SUMPRODUCT (SUM will do fine) since you are using TRANSPOSE and the
formula needs to be array entered. If you want to compare an array the
formula I provided is the way to go

--


Regards,


Peo Sjoblom