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" <rk@discussions.microsoft.com> wrote in message 
news:452AD9D0-F114-418D-A814-3F27425DF7F5@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. 

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