Sumproduct with Matched Criteria an Extracted Text

[Apple1]

This is a Qn pertaining to


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]

<colgroup><col style="width:48pt" width="64" span="8"> </colgroup><tbody>
[TD="class: xl65, width: 64"] [/TD]
[TD="class: xl67, width: 64"] A [/TD]
[TD="class: xl67, width: 64"] B [/TD]
[TD="class: xl67, width: 64"] C
[/TD]
[TD="class: xl67, width: 64"] D [/TD]
[TD="class: xl67, width: 64"] E [/TD]
[TD="class: xl67, width: 64"] F [/TD]
[TD="class: xl67, width: 64"] G [/TD]

[TD="class: xl67, width: 64"] 1
[/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl69, width: 64"]2001
[/TD]
[TD="class: xl69, width: 64"]2001
[/TD]
[TD="class: xl69, width: 64"]2002[/TD]
[TD="class: xl69, width: 64"]2002[/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl69, width: 64"]2002CY-Q1
[/TD]

[TD="class: xl67, width: 64"] 2 [/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl66, width: 64"]Sales[/TD]
[TD="class: xl66, width: 64"]Profit[/TD]
[TD="class: xl66, width: 64"]Sales[/TD]
[TD="class: xl66, width: 64"]Profit[/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl66, width: 64"]Sales[/TD]

[TD="class: xl67, width: 64"] 3 [/TD]
[TD="class: xl66, width: 64"]James[/TD]
[TD="class: xl68, width: 64"]1000[/TD]
[TD="class: xl68, width: 64"]690[/TD]
[TD="class: xl68, width: 64"]8559[/TD]
[TD="class: xl68, width: 64"]1895[/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl68, width: 64"][/TD]

[TD="class: xl67, width: 64"] 4 [/TD]
[TD="class: xl66, width: 64"]Peter[/TD]
[TD="class: xl68, width: 64"]2000[/TD]
[TD="class: xl68, width: 64"]756[/TD]
[TD="class: xl68, width: 64"]4560[/TD]
[TD="class: xl68, width: 64"]1596
[/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]

[TD="class: xl67, width: 64"] 5 [/TD]
[TD="class: xl66, width: 64"]Tom[/TD]
[TD="class: xl68, width: 64"]3000[/TD]
[TD="class: xl68, width: 64"]56[/TD]
[TD="class: xl68, width: 64"]1520[/TD]
[TD="class: xl68, width: 64"]3698[/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]

[TD="class: xl67, width: 64"] 6 [/TD]
[TD="class: xl66, width: 64"]Edwin[/TD]
[TD="class: xl68, width: 64"]4000[/TD]
[TD="class: xl68, width: 64"]56[/TD]
[TD="class: xl68, width: 64"]1563[/TD]
[TD="class: xl68, width: 64"]4563[/TD]
[TD="class: xl65, width: 64"][/TD]

</tbody>

[/TD]
[/TR]
</tbody>[/TABLE]

G3=SUMPRODUCT(($B$1:$E$1=mid(G1,3,4))*($B$2:$E$2=G2)*($B$3:$E$6))

Why doesn't this formula work?

Thank you

 

[Aladin Akyurek]

Re: Sumporduct with Matched Criteria an Extracted Text

Do not discuss a problem you have in terms of non-working formulas. It's better for any would-be helper (including me) to have it in words. That said:

Is it the intention to use the 2002 bit of the condition given in G1?

 

[Apple1]

Re: Sumporduct with Matched Criteria an Extracted Text

Do not discuss a problem you have in terms of non-working formulas. It's better for any would-be helper (including me) to have it in words. That said:

Is it the intention to use the 2002 bit of the condition given in G1?

Hi

The problem lies with "mid(G1,3,4)". But I am using the formula within a formatted table where the header of the column will be in "CY...", so the formula will have to extract the Year from "CY.."

Thank you.

 

[Apple1]

Re: Sumporduct with Matched Criteria an Extracted Text

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][TABLE="class: cms_table"]
<tbody>[TR]
[TD="class: cms_table_xl65, width: 64"][/TD]
[TD="class: cms_table_xl67, width: 64"]A[/TD]
[TD="class: cms_table_xl67, width: 64"]B[/TD]
[TD="class: cms_table_xl67, width: 64"]C[/TD]
[TD="class: cms_table_xl67, width: 64"]D[/TD]
[TD="class: cms_table_xl67, width: 64"]E[/TD]
[TD="class: cms_table_xl67, width: 64"]F
[/TD]
[TD="class: cms_table_xl67, width: 64"]G
[/TD]
[TD="class: cms_table_xl67, width: 64"]H
[/TD]
[TD="class: cms_table_xl67, width: 64"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl67, width: 64"]1
[/TD]
[TD="class: cms_table_xl65, width: 64"][/TD]
[TD="class: cms_table_xl69, width: 64"]2001[/TD]
[TD="class: cms_table_xl69, width: 64"]2001[/TD]
[TD="class: cms_table_xl69, width: 64"]2002[/TD]
[TD="class: cms_table_xl69, width: 64"]2002[/TD]
[TD="class: cms_table_xl65, width: 64"][/TD]
[TD="class: cms_table_xl69, width: 64"]2002CY-Q1
[/TD]
[TD="class: cms_table_xl69, width: 64"]2003CY-Q2
[/TD]
[TD="class: cms_table_xl69, width: 64"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl67, width: 64"]2[/TD]
[TD="class: cms_table_xl65, width: 64"][/TD]
[TD="class: cms_table_xl66, width: 64"]Sales[/TD]
[TD="class: cms_table_xl66, width: 64"]Profit[/TD]
[TD="class: cms_table_xl66, width: 64"]Sales[/TD]
[TD="class: cms_table_xl66, width: 64"]Profit[/TD]
[TD="class: cms_table_xl65, width: 64"][/TD]
[TD="class: cms_table_xl66, width: 64"]SUMPRODUCT($B$3:$E$6*(($B$1:$E$1=mid(G1,3,4))*($B$2:$E$2=I1)))
[/TD]
[TD="class: cms_table_xl66, width: 64"]SUMPRODUCT($B$3:$E$6*(($B$1:$E$1=mid(H1,3,4))*($B$2:$E$2=I1)))
[/TD]
[TD="class: cms_table_xl66, width: 64"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl67, width: 64"]3[/TD]
[TD="class: cms_table_xl66, width: 64"]James[/TD]
[TD="class: cms_table_xl68, width: 64"]1000[/TD]
[TD="class: cms_table_xl68, width: 64"]690[/TD]
[TD="class: cms_table_xl68, width: 64"]8559[/TD]
[TD="class: cms_table_xl68, width: 64"]1895[/TD]
[TD="class: cms_table_xl65, width: 64"][/TD]
[TD="class: cms_table_xl68, width: 64"][/TD]
[TD="class: cms_table_xl68, width: 64"][/TD]
[TD="class: cms_table_xl68, width: 64"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl67, width: 64"]4[/TD]
[TD="class: cms_table_xl66, width: 64"]Peter[/TD]
[TD="class: cms_table_xl68, width: 64"]2000[/TD]
[TD="class: cms_table_xl68, width: 64"]756[/TD]
[TD="class: cms_table_xl68, width: 64"]4560[/TD]
[TD="class: cms_table_xl68, width: 64"]1596[/TD]
[TD="class: cms_table_xl65, width: 64"][/TD]
[TD="class: cms_table_xl65, width: 64"][/TD]
[TD="class: cms_table_xl65, width: 64"][/TD]
[TD="class: cms_table_xl65, width: 64"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl67, width: 64"]5[/TD]
[TD="class: cms_table_xl66, width: 64"]Tom[/TD]
[TD="class: cms_table_xl68, width: 64"]3000[/TD]
[TD="class: cms_table_xl68, width: 64"]56[/TD]
[TD="class: cms_table_xl68, width: 64"]1520[/TD]
[TD="class: cms_table_xl68, width: 64"]3698[/TD]
[TD="class: cms_table_xl65, width: 64"][/TD]
[TD="class: cms_table_xl65, width: 64"][/TD]
[TD="class: cms_table_xl65, width: 64"][/TD]
[TD="class: cms_table_xl65, width: 64"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl67, width: 64"]6[/TD]
[TD="class: cms_table_xl66, width: 64"]Edwin[/TD]
[TD="class: cms_table_xl68, width: 64"]4000[/TD]
[TD="class: cms_table_xl68, width: 64"]56[/TD]
[TD="class: cms_table_xl68, width: 64"]1563[/TD]
[TD="class: cms_table_xl68, width: 64"]4563[/TD]
[TD="class: cms_table_xl65, width: 64"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Specially refer to the above table for an Eg hwere the header are in "CY..". Thank you

 

[Aladin Akyurek]

Re: Sumporduct with Matched Criteria an Extracted Text

Thus, it's the year from G1 which is one of the conditions...

In G3 enter:

=SUMPRODUCT($B$3:$E$6*((ISNUMBER(FIND($B$1:$E$1,$G1))*($B$2:$E$2=$G2))))

Note. Please do not propose yourself formula(s) for a problem you seek help for. Such is more often than not misleading about the problem itself.

 

[Apple1]

Re: Sumporduct with Matched Criteria an Extracted Text

Thus, it's the year from G1 which is one of the conditions...

In G3 enter:

=SUMPRODUCT($B$3:$E$6*((ISNUMBER(FIND($B$1:$E$1,$G1))*($B$2:$E$2=$G2))))

Note. Please do not propose yourself formula(s) for a problem you seek help for. Such is more often than not misleading about the problem itself.

Hi

This is a bit disappointing and time wasting. You clearly don't understand the problem.

Can someone else help to solve this problem?

Thank you

 

[barry houdini]

Re: Sumporduct with Matched Criteria an Extracted Text

This is a bit disappointing and time wasting. You clearly don't understand the problem.

Hello Apple1, perhaps you noticed that Aladin is a MrExcel MVP and has 80,000+ posts here, I think it's unlikely he doesn't understand the problem.

I tried the formula that Aladin proposed, based on the setup you showed in your first post. The result I get is 16,202, which seems correct to me. Is that the result you wanted?

 

[MARZIOTULLIO]

Re: Sumporduct with Matched Criteria an Extracted Text

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD][/TD]
[TD]

2001​

[/TD]
[TD]

2001​

[/TD]
[TD]

2002​

[/TD]
[TD]

2002​

[/TD]
[TD][/TD]
[TD]2002CY-Q1[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD][/TD]
[TD]Sales[/TD]
[TD]Profit[/TD]
[TD]Sales[/TD]
[TD]Profit[/TD]
[TD][/TD]
[TD]Sales[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]James[/TD]
[TD]

1000​

[/TD]
[TD]

690​

[/TD]
[TD]

8559​

[/TD]
[TD]

1895​

[/TD]
[TD][/TD]
[TD]

16202​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]Peter[/TD]
[TD]

2000​

[/TD]
[TD]

756​

[/TD]
[TD]

4560​

[/TD]
[TD]

1596​

[/TD]
[TD][/TD]
[TD]

16202​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]Tom[/TD]
[TD]

3000​

[/TD]
[TD]

56​

[/TD]
[TD]

1520​

[/TD]
[TD]

3698​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]Edwin[/TD]
[TD]

4000​

[/TD]
[TD]

56​

[/TD]
[TD]

1563​

[/TD]
[TD]

4563​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

G3=SUMPRODUCT((B2:E2=G2)*((LEFT(G1,4)+0=B1:E1))*(B3:E6))

other solution

G4=SUMPRODUCT((B2:E2=G2)*((SUBSTITUTE(G1,"CY-Q1","")+0=B1:E1))*(B3:E6))