SUMIFS with additional IFs

[drluke]

Sheet 1

Client	Sales	Returns
A01
A02

<colgroup><col width="64" style="width: 48pt;" span="3"><tbody>
</tbody>

Sheet 2

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

<colgroup><col width="64" style="width: 48pt;" span="3"><tbody>
</tbody>

In sheet 1 I want to sum sales of products by client, so I need my sum formula to:
(a) look in col B and E (sheet 2) to find the client (they never change and always contain text)
(b) treat clients A01 and A01-5 as if they are the same (row 1 Sheet 1) Any suggestions for a solution much appreciated.

<colgroup><col width="64" style="width: 48pt;" span="8"> <tbody> </tbody>

 

[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

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​
1​	Client​	Sales​			Product​	Client​	Sales​	Product​	Client​	Sales​
2​	A01​	5041​			Blue​	A01​	51​	Red​	A02​	32​
3​	A02​	3698​			Blue​	A02​	895​	Red​	A01​	947​
4​					Blue​	A01​	256​	Red​	A02​	367​
5​					Blue​	A01-5​	226​	Red​	A02​	458​
6​					Blue​	A02​	1005​	Red​	A01​	2568​
7​					Blue​	A02​	3​	Red​	A01-5​	66​
8​					Blue​	A01-5​	626​	Red​	A02​	749​
9​					Blue​	A02​	189​	Red​	A01-5​	301​

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

M.