SumProduct with using Left() Function

**rmb623** · 01-06-2011, 08:33 PM

This formula is returning 0 and I do not know why. The colums are correct in the formula.
What I am trying to do is write a formula that says if the first three letters column B in Portfolio Data are "TRA" and on the same row in column E there is NA Gas, then sum up all of those values that meet that criteris in column V. I am using excel 2002.

=SUMPRODUCT(--(LEFT(PORTFOLIO_DATA!$B$2:$B$19000,3)="TRA"),--(PORTFOLIO_DATA!$E$2:$E$19000="NA Gas"),PORTFOLIO_DATA!V$2:V$19000)

**daddylonglegs** · 01-06-2011, 08:44 PM

That formula looks OK to me, perhaps you have a problem with the data.

One possibility is that your column to sum has text-formatted numbers, what do you get with

=SUMPRODUCT(--(LEFT(PORTFOLIO_DATA!$B$2:$B$19000,3)="TRA"),--(PORTFOLIO_DATA!$E$2:$E$19000="NA Gas"),PORTFOLIO_DATA!V$2:V$19000+0)

Try testing each of the other ranges with a COUNTIF, e.g.

=COUNTIF(PORTFOLIO_DATA!$B$2:$B$19000,"TRA*")

and

=COUNTIF(PORTFOLIO_DATA!$E$2:$E$19000,"NA Gas")

do you get any non-zero results?

**6StringJazzer** · 01-06-2011, 08:44 PM

I think if you look at DonkeyOte's post in your other thread on this question you will find his formula will solve it nicely.

**rmb623** · 01-07-2011, 12:25 PM

Originally Posted by 6StringJazzer

I think if you look at DonkeyOte's post in your other thread on this question you will find his formula will solve it nicely.

his sumifs formula doesnt work in 2002

**rmb623** · 01-07-2011, 12:34 PM

that solved it daddylonglegs. thanks

**vahiddastitash** · 05-16-2013, 05:25 AM

Hi everybody i use below formula and it is very slow and take along time to update sheet.Please help me to speed up.
=SUMPRODUCT(--(LEFT(All!$D:$D;4)=C$2);--(All!$A:$A=Total!$A$13);--(All!$N:$N=Total!$N3);All!$G:$G)

**arlu1201** · 05-16-2013, 05:53 AM

vahiddastitash,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.