SUMIFS with additional IFs

[drluke]

Sheet 1

[TABLE="width: 150"]
<colgroup><col width="64" style="width: 48pt;" span="3"><tbody>[TR]
[TD="width: 64, bgcolor: transparent"]Client
[/TD]
[TD="width: 64, bgcolor: white"]Sales
[/TD]
[TD="width: 64, bgcolor: white"]Returns
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] A01
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] A02
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2
[TABLE="width: 150"]
<colgroup><col width="64" style="width: 48pt;" span="3"><tbody>[TR]
[TD="width: 64, bgcolor: #D7E4BC"]Product
[/TD]
[TD="width: 64, bgcolor: transparent"]Client
[/TD]
[TD="width: 64, bgcolor: white"]Sales
[/TD]
[TD="width: 64, bgcolor: white"]Product
[/TD]
[TD="width: 64, bgcolor: white"]Client
[/TD]
[TD="width: 64, bgcolor: white"]Sales
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] Blue
[/TD]
[TD="bgcolor: transparent"] A01
[/TD]
[TD="bgcolor: transparent"]51
[/TD]
[TD="bgcolor: transparent"]Red
[/TD]
[TD="bgcolor: transparent"]A02
[/TD]
[TD="bgcolor: transparent"]32
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] Blue
[/TD]
[TD="bgcolor: transparent"] A02
[/TD]
[TD="bgcolor: transparent"]895
[/TD]
[TD="bgcolor: transparent"]Red
[/TD]
[TD="bgcolor: transparent"]A01
[/TD]
[TD="bgcolor: transparent"]947
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] Blue
[/TD]
[TD="bgcolor: transparent"] A01
[/TD]
[TD="bgcolor: transparent"]256
[/TD]
[TD="bgcolor: transparent"]Red
[/TD]
[TD="bgcolor: transparent"]A02
[/TD]
[TD="bgcolor: transparent"]367
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] Blue
[/TD]
[TD="bgcolor: transparent"] A01-5
[/TD]
[TD="bgcolor: transparent"]226
[/TD]
[TD="bgcolor: transparent"]Red
[/TD]
[TD="bgcolor: transparent"]A02
[/TD]
[TD="bgcolor: transparent"]458
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] Blue
[/TD]
[TD="bgcolor: transparent"] A02
[/TD]
[TD="bgcolor: transparent"]1005
[/TD]
[TD="bgcolor: transparent"]Red
[/TD]
[TD="bgcolor: transparent"]A01
[/TD]
[TD="bgcolor: transparent"]2568
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] Blue
[/TD]
[TD="bgcolor: transparent"] A02
[/TD]
[TD="bgcolor: transparent"]3
[/TD]
[TD="bgcolor: transparent"]Red
[/TD]
[TD="bgcolor: transparent"]A01-5
[/TD]
[TD="bgcolor: transparent"]66
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] Blue
[/TD]
[TD="bgcolor: transparent"] A01-5
[/TD]
[TD="bgcolor: transparent"]626
[/TD]
[TD="bgcolor: transparent"]Red
[/TD]
[TD="bgcolor: transparent"]A02
[/TD]
[TD="bgcolor: transparent"]749
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] Blue
[/TD]
[TD="bgcolor: transparent"] A02
[/TD]
[TD="bgcolor: transparent"]189
[/TD]
[TD="bgcolor: transparent"]Red
[/TD]
[TD="bgcolor: transparent"]A01-5
[/TD]
[TD="bgcolor: transparent"]301
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 384"]
<colgroup><col width="64" style="width: 48pt;" span="8"> <tbody>[TR]
[TD="width: 448, bgcolor: transparent, colspan: 7"]In sheet 1 I want to sum sales of products by client, so I need my sum formula to:
[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, colspan: 8"](a) look in col B and E (sheet 2) to find the client (they never change and always contain text)
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, colspan: 6"](b) treat clients A01 and A01-5 as if they are the same (row 1 Sheet 1)

Any suggestions for a solution much appreciated.

[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]

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

 

[Ron Coderre]

Using Power Query (that's probably already in your version of Excel 2010 or later)..
PQ will easily convert your data into this structure:

Product      Client      Sales      Parent Client
Blue         A01            51      A01
Red          A02            32      A02
Blue         A02           895      A02
Red          A01           947      A01
Blue         A01           256      A01
Red          A02           367      A02
Blue         A01-5         226      A01
Red          A02           458      A02
Blue         A02          1005      A02
Red          A01          2568      A01
Blue         A02             3      A02
Red          A01-5          66      A01
Blue         A01-5         626      A01
Red          A02           749      A02
Blue         A02           189      A02
Red          A01-5         301      A01

If you're interested, let me know and I'll post the query that creates that.

 

[drluke]

I am using Excel 2007

 

[Aladin Akyurek]

What are the expected results?

 

[drluke]

Summary of sales for all products by customer:

A01 5841
A02 3698

 

[MARZIOTULLIO]

Why A01 5841 ?

 

[Marcelo Branco]

Maybe


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][td="bgcolor: #DCE6F1"]

G​

[/td][td="bgcolor: #DCE6F1"]

H​

[/td][td="bgcolor: #DCE6F1"]

I​

[/td][td="bgcolor: #DCE6F1"]

J​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Client​

[/td][td]

Sales​

[/td][td][/td][td][/td][td]

Product​

[/td][td]

Client​

[/td][td]

Sales​

[/td][td]

Product​

[/td][td]

Client​

[/td][td]

Sales​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

A01​

[/td][td]

5041​

[/td][td][/td][td][/td][td]

Blue​

[/td][td]

A01​

[/td][td]

51​

[/td][td]

Red​

[/td][td]

A02​

[/td][td]

32​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

A02​

[/td][td]

3698​

[/td][td][/td][td][/td][td]

Blue​

[/td][td]

A02​

[/td][td]

895​

[/td][td]

Red​

[/td][td]

A01​

[/td][td]

947​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td][/td][td][/td][td][/td][td][/td][td]

Blue​

[/td][td]

A01​

[/td][td]

256​

[/td][td]

Red​

[/td][td]

A02​

[/td][td]

367​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td][/td][td][/td][td][/td][td][/td][td]

Blue​

[/td][td]

A01-5​

[/td][td]

226​

[/td][td]

Red​

[/td][td]

A02​

[/td][td]

458​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td][/td][td][/td][td][/td][td][/td][td]

Blue​

[/td][td]

A02​

[/td][td]

1005​

[/td][td]

Red​

[/td][td]

A01​

[/td][td]

2568​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td][/td][td][/td][td][/td][td][/td][td]

Blue​

[/td][td]

A02​

[/td][td]

3​

[/td][td]

Red​

[/td][td]

A01-5​

[/td][td]

66​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td][/td][td][/td][td][/td][td][/td][td]

Blue​

[/td][td]

A01-5​

[/td][td]

626​

[/td][td]

Red​

[/td][td]

A02​

[/td][td]

749​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td][/td][td][/td][td][/td][td][/td][td]

Blue​

[/td][td]

A02​

[/td][td]

189​

[/td][td]

Red​

[/td][td]

A01-5​

[/td][td]

301​

[/td][/tr]
[/table]

Formula in B2 copied down
=SUMIF($F$2:$I$9,A2&"*",$G$2:$J$9)

M.