Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to Count the number of "rows" (or Array items) included in a Sumif formula? | Excel Discussion (Misc queries) | |||
Application hang with array sum(sumif... formula | Excel Worksheet Functions | |||
Array Formula w/ Multiple SumIf Criteria | Excel Worksheet Functions | |||
Modify SumIF... Array Formula | Excel Worksheet Functions | |||
Modify SumIF... Array Formula | Excel Worksheet Functions |