Is there a way to use a multiple column array in SUMPRODUCT?

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.

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" wrote in message
...
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.

=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.