Sumproduct question

[cwallace70]

I would like to perform a sumproduct for the following but I keep getting value as the sum. The formula is as follows
=SUMPRODUCT(--(A2:A50000="LU"),--(LEFT(E2:E50000,1)="K"),--(H2:H50000))

My spreadsheet have a dept in column A, a code in column E and an amount in column H.
I want to get a sum if column A have the dept code of "LU" and a code of "H" then sum all the amounts in column H that fits those first two criteria's. I am using excel 2003 and cannot get this formula to work. Please help. What am I doing wrong

 

[Norie]

Is the there something missing from the last argument? --(H2:H50000)

If not I don't think you need/want to use the -- there.

 

[Matty]

Welcome to the Board!

Try:

=SUMPRODUCT(--(A2:A50000="LU"),--(LEFT(E2:E50000,1)="K"),H2:H50000)

Matty

 

[cwallace70]

I am a very happy person. It worked! Thank you very much

 

[cwallace70]

To expand on my previous question, I have Tabs Jan to Dec. with column A having the the dept code "LU" and column E having the code "H" and column H having the amounts I want to sum. How can I get this to all sum up to a summary sheet? Please help this is driving me crazy

 

[Fazza]

Well, if your data actually goes to 50,000 rows on each sheet I suggest you don't use formulas - that many rows across 12 sheets will be slow to calculate, I expect. Instead maybe a pivot table. What do you think?

 

[cwallace70]

I was thinking about a pivot table, but when I tried to execute it, I kept getting an error message

 

[cwallace70]

My spreadsheet have a dept in column A, a code in column E and an amount in column H.
I want to get a sum if column A have the dept code of "LU" and a code of "H" then sum all the amounts in column H that fits those first two criteria's. you gave me this formula =SUMPRODUCT(--(A2:A50000="LU"),--(LEFT(E2:E50000,1)="K"),H2:H50000) which worked great, but is there a formula I can use so that I do not have to hard code each dept code, instead I want it to look any where on thespread sheet and find the sum of, for example dept 33 with a H code and a sum.
is that possible?

 

[Aladin Akyurek]

My spreadsheet have a dept in column A, a code in column E and an amount in column H.
I want to get a sum if column A have the dept code of "LU" and a code of "H" then sum all the amounts in column H that fits those first two criteria's. you gave me this formula =SUMPRODUCT(--(A2:A50000="LU"),--(LEFT(E2:E50000,1)="K"),H2:H50000) which worked great, but is there a formula I can use so that I do not have to hard code each dept code, instead I want it to look any where on thespread sheet and find the sum of, for example dept 33 with a H code and a sum.
is that possible?

J2: LU

J3: LQ

K1: H

L1: U

K2, just enter, copy across, and down:

=SUMPRODUCT(--($A$2:$A$50000=$J2),--(LEFT($E$2:$E$50000,1)=K$1),$H$2:$H$50000)

 

[cwallace70]

I copied and paste the formula but I don't quite understand it and it is not working. Please explain.

Thanks