sumif as array formula
 

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!P4:P50,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

sumif as array formula
 

Try

=SUMPRODUCT(--(ISNUMBER(MATCH(Sheet1!S1:S100,Sales!P4:P50,0))),S heet1!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


"Tim Green" wrote in message
...
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!P4:P50,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

sumif as array formula
 

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

http://www.xldynamic.com/source/xld.SUMPRODUCT.html#new
--
Best Regards,

Luke M


"Tim Green" wrote:

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!P4:P50,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

sumif as array formula
 

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:$P5 0)))*'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.

"Luke M" wrote:

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

http://www.xldynamic.com/source/xld.SUMPRODUCT.html#new
--
Best Regards,

Luke M


"Tim Green" wrote:

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!P4:P50,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

sumif as array formula
 

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


"Tim Green" wrote in message
...
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:$P5 0)))*'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.

"Luke M" wrote:

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

http://www.xldynamic.com/source/xld.SUMPRODUCT.html#new
--
Best Regards,

Luke M


"Tim Green" wrote:

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!P4:P50,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