SUMPRODUCT with LEFT or RIGHT function combination

[j312ry]

Hi All Gurus,

<TABLE style="WIDTH: 276pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=367 x:str><COLGROUP><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2560" width=70><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2889" width=79><COL style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 3291" width=90><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 53pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17 width=70>Material</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" width=64>Plant</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 59pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" width=79>Movement</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 68pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" width=90>Cost Center</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" width=64>Quantity</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl22 height=17 x:num>800000</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl22 x:num>2650</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl22 x:num>201</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl22>2650TA</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl22 x:num>5</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl22 height=17 x:num>800000</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl22 x:num>2650</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl22 x:num>201</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl22>2650TP</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl22 x:num>10</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl22 height=17 x:num>800000</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl22 x:num>2650</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl22 x:num>202</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl22>2650TP</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl22 x:num>15</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl22 height=17 x:num>800000</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl22 x:num>2650</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl22 x:num>203</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl22>2650TP</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl22 x:num>20</TD></TR></TBODY></TABLE>

If i have data like that and i want to sum the Quantity with below conditions :
- Movement 201 or 202
- Cost Center xxxxTP

In this example the total of SUMPRODUCT = 25

How to generate it using SUMPRODUCT? a solution with VBA Code or Excel is OK. Actually i will use EVALUATE to get the result.

Please kindly advice..
Thanks before..

 

[Andrew Poulsom]

Try:

=SUMPRODUCT(((C2:C5=201)+(C2:C5=202)),--(RIGHT(D2:D5,2)="TP"),E2:E5)

 

[j312ry]

I can execute the first condition with :

xTotal = Evaluate("=SUMPRODUCT(--(((C2:C5=201)+(C2:C5=202))>0),E2:E5)")

But then, i stuck on second condition, please help.

 

[j312ry]

You are my saviour Andrew...Thanks a lottttt

Try:

=SUMPRODUCT(((C2:C5=201)+(C2:C5=202)),--(RIGHT(D2:D5,2)="TP"),E2:E5)

 

[p45cal]

=sumproduct((right($j$14:$j$17,2)="tp")*((i14:i17=202)+(i14:i17=201)),k14:k17)

 

[j312ry]

Many thanks pascal, your solution are works too..
Got another variant of formula with SUMPRODUCT

=sumproduct((right($j$14:$j$17,2)="tp")*((i14:i17=202)+(i14:i17=201)),k14:k17)

 

[vahiddastitash]

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)

 

[Andrew Poulsom]

Try to avoid referencing entire columns.

 

[aaalviny]

Hi All Gurus,

[TABLE="width: 276"]
<colgroup><col style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2560" width="70"><col style="WIDTH: 48pt" width="64"><col style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2889" width="79"><col style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 3291" width="90"><col style="WIDTH: 48pt" width="64"></colgroup><tbody>[TR]
[TD="width: 70, bgcolor: transparent"]Material[/TD]
[TD="width: 64, bgcolor: transparent"]Plant[/TD]
[TD="width: 79, bgcolor: transparent"]Movement[/TD]
[TD="width: 90, bgcolor: transparent"]Cost Center[/TD]
[TD="width: 64, bgcolor: transparent"]Quantity[/TD]
[/TR]
[TR]
[TD="class: xl22, bgcolor: transparent"]800000[/TD]
[TD="class: xl22, bgcolor: transparent"]2650[/TD]
[TD="class: xl22, bgcolor: transparent"]201[/TD]
[TD="class: xl22, bgcolor: transparent"]2650TA[/TD]
[TD="class: xl22, bgcolor: transparent"]5[/TD]
[/TR]
[TR]
[TD="class: xl22, bgcolor: transparent"]800000[/TD]
[TD="class: xl22, bgcolor: transparent"]2650[/TD]
[TD="class: xl22, bgcolor: transparent"]201[/TD]
[TD="class: xl22, bgcolor: transparent"]2650TP[/TD]
[TD="class: xl22, bgcolor: transparent"]10[/TD]
[/TR]
[TR]
[TD="class: xl22, bgcolor: transparent"]800000[/TD]
[TD="class: xl22, bgcolor: transparent"]2650[/TD]
[TD="class: xl22, bgcolor: transparent"]202[/TD]
[TD="class: xl22, bgcolor: transparent"]2650TP[/TD]
[TD="class: xl22, bgcolor: transparent"]15[/TD]
[/TR]
[TR]
[TD="class: xl22, bgcolor: transparent"]800000[/TD]
[TD="class: xl22, bgcolor: transparent"]2650[/TD]
[TD="class: xl22, bgcolor: transparent"]203[/TD]
[TD="class: xl22, bgcolor: transparent"]2650TP[/TD]
[TD="class: xl22, bgcolor: transparent"]20[/TD]
[/TR]
</tbody>[/TABLE]


If i have data like that and i want to sum the Quantity with below conditions :
- Movement 201 or 202
- Cost Center xxxxTP

In this example the total of SUMPRODUCT = 25

How to generate it using SUMPRODUCT? a solution with VBA Code or Excel is OK. Actually i will use EVALUATE to get the result.

Please kindly advice..
Thanks before..

create three new columns (better before the whole data)
A2: =IF(OR($E2=201, $E2=202), 1, 0)
B2: =IF(RIGHT($F2, 2)="TP", 1, 0)
C2: =$A2*$B2
then a simple SUMIF($C$2:$C$100, 1, $H$2:$H$100) does the job
easy to read and understand those short formulas, right?

 

[vahiddastitash]

How? I should use left() function and i don't know how could i do your comment without refrence.
I should search 3 item in 3 column and for one of them i should use Left() function please help me again.