Is there a way to use a multiple column array in SUMPRODUCT?
in [Functions]
|
From: rk on 15 Mar 2010 17:31 I am attempting to use SUMPRODUCT to pull values froma multiple column spreadsheet and add them together. I am able to get the SUMPRODUCT to work if I am only looking for values in one column, but as soon as I select all columns, the function no longer works. example: column A holds accounts, column B holds subaccounts, columns D - F (possibility of over 100 columns) hold companies I am looking for all values to be added together that hold values for both column A and column B =SUMPRODUCT(--(A2:A61=A44),--(B2:B61=B44)--(F2:F61)) works for just column F, but when I try to add more columns, i.e., D - F, the function no longer works. Is there a way to expand this function or nest another function (maybe SUMIF) to do this? A B C D E F 100 121 account descrip 89.00 - 900.00 200 131 account descrip - - 10,000.00 300 141 account descrip - - 101.50 400 151 account descrip - - 895.30 500 161 account descrip - - - So with the formula I have now, I am getting an answer of 900.00, but what I would really like to get is the answer 989.00.
From: T. Valko on 15 Mar 2010 17:57 Maybe something like this... =SUMPRODUCT((A2:A61=A44)*(B2:B61=B44)*D2:F61) If the range to sum also contains TEXT this will not work. -- Biff Microsoft Excel MVP "rk" <rk(a)discussions.microsoft.com> wrote in message news:452AD9D0-F114-418D-A814-3F27425DF7F5(a)microsoft.com... >I am attempting to use SUMPRODUCT to pull values froma multiple column > spreadsheet and add them together. I am able to get the SUMPRODUCT to > work > if I am only looking for values in one column, but as soon as I select all > columns, the function no longer works. > > example: column A holds accounts, column B holds subaccounts, columns D - > F > (possibility of over 100 columns) hold companies > > I am looking for all values to be added together that hold values for both > column A and column B > > =SUMPRODUCT(--(A2:A61=A44),--(B2:B61=B44)--(F2:F61)) works for just column > F, but when I try to add more columns, i.e., D - F, the function no longer > works. > > Is there a way to expand this function or nest another function (maybe > SUMIF) to do this? > > A B C D E F > 100 121 account descrip 89.00 - 900.00 > 200 131 account descrip - - 10,000.00 > 300 141 account descrip - - 101.50 > 400 151 account descrip - - 895.30 > 500 161 account descrip - - - > > So with the formula I have now, I am getting an answer of 900.00, but what > I > would really like to get is the answer 989.00.
From: Teethless mama on 15 Mar 2010 18:34 > =SUMPRODUCT(--(A2:A61=A44),--(B2:B61=B44)--(F2:F61)) works for just column > F, but when I try to add more columns, i.e., D - F, the function no longer > works. Try it like this: =SUMPRODUCT((A2:A61=A44)*(B2:B61=B44)*D2:F61) "rk" wrote: > I am attempting to use SUMPRODUCT to pull values froma multiple column > spreadsheet and add them together. I am able to get the SUMPRODUCT to work > if I am only looking for values in one column, but as soon as I select all > columns, the function no longer works. > > example: column A holds accounts, column B holds subaccounts, columns D - F > (possibility of over 100 columns) hold companies > > I am looking for all values to be added together that hold values for both > column A and column B > > =SUMPRODUCT(--(A2:A61=A44),--(B2:B61=B44)--(F2:F61)) works for just column > F, but when I try to add more columns, i.e., D - F, the function no longer > works. > > Is there a way to expand this function or nest another function (maybe > SUMIF) to do this? > > A B C D E F > 100 121 account descrip 89.00 - 900.00 > 200 131 account descrip - - 10,000.00 > 300 141 account descrip - - 101.50 > 400 151 account descrip - - 895.30 > 500 161 account descrip - - - > > So with the formula I have now, I am getting an answer of 900.00, but what I > would really like to get is the answer 989.00.
|
Pages: 1 Prev: cube functions Next: Main Form - copy exact data from multiple worksheets with live lin |