Sumproduct with Matched Criteria an Extracted Text

[Apple1]

This is a Qn pertaining to

A B C D E F G 1 2001 2001 2002 2002 2002CY-Q1 2 Sales Profit Sales Profit Sales 3 James 1000 690 8559 1895 4 Peter 2000 756 4560 1596 5 Tom 3000 56 1520 3698 6 Edwin 4000 56 1563 4563 <colgroup><col style="width:48pt" width="64" span="8"> </colgroup><tbody> </tbody>

<tbody>
</tbody>

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

A B C D E F G H 1 2001 2001 2002 2002 2002CY-Q1 2003CY-Q2 2 Sales Profit Sales Profit SUMPRODUCT($B$3:$E$6*(($B$1:$E$1=mid(G1,3,4))*($B$2:$E$2=I1))) SUMPRODUCT($B$3:$E$6*(($B$1:$E$1=mid(H1,3,4))*($B$2:$E$2=I1))) 3 James 1000 690 8559 1895 4 Peter 2000 756 4560 1596 5 Tom 3000 56 1520 3698 6 Edwin 4000 56 1563 4563 <tbody> </tbody>

<tbody>
</tbody>

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

	A​	B​	C​	D​	E​	F​	G​
1​		2001​	2001​	2002​	2002​		2002CY-Q1
2​		Sales	Profit	Sales	Profit		Sales
3​	James	1000​	690​	8559​	1895​		16202​
4​	Peter	2000​	756​	4560​	1596​		16202​
5​	Tom	3000​	56​	1520​	3698​
6​	Edwin	4000​	56​	1563​	4563​

<tbody>
</tbody>

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))