Sumif, Min & Matching Cells

[l_doherty]

Hi Guys,

I need some help finding a formula that will give me the total sales units of an item where I need to add the sales units of the "Parent" and the minimum sales units of the "Box" that relate to the item.

For example below, Item 1 is the sum of Item 1 (Parent) + minimum of (Item 1 Box 1) and (Item 1 Box 2), therefore 5 + 1 = 6.

I would like to display the totals by Master SKU in a separate table, as shown in Table 2.

Is there a forumula where I can use the "Master SKU" column and the "Parent/Box" column that will give me the correct answer?

Table 1 - Raw Data
[TABLE="width: 500"]
<tbody>[TR]
[TD]SKU Description[/TD]
[TD]Master SKU[/TD]
[TD]Parent/Box[/TD]
[TD]Sales Units[/TD]
[/TR]
[TR]
[TD]Item 1 (Parent)[/TD]
[TD]Item 1[/TD]
[TD]Parent[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Item 1 (Box 1)[/TD]
[TD]Item 1[/TD]
[TD]Box[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Item 1 (Box 2)[/TD]
[TD]Item 1[/TD]
[TD]Box[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Item 2 (Parent)[/TD]
[TD]Item 2[/TD]
[TD]Parent[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]Item 2 (Box 1)[/TD]
[TD]Item 2[/TD]
[TD]Box[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Item 2 (Box 2)[/TD]
[TD]Item 2[/TD]
[TD]Box[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Item 2 (Box 3)[/TD]
[TD]Item 2[/TD]
[TD]Box[/TD]
[TD]6[/TD]
[/TR]
</tbody>[/TABLE]

Table 2 - Total Sales by Master SKU
[TABLE="width: 500"]
<tbody>[TR]
[TD]Master SKU[/TD]
[TD]Total Sales Units[/TD]
[/TR]
[TR]
[TD]Item 1[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Item 2[/TD]
[TD]25[/TD]
[/TR]
</tbody>[/TABLE]

Thanks in advance for your help!

 

[jtakw]

Hi,

If you can add the Box # to Parent/Box column in Table 1, it's much simpler, like below:

Excel 2010
	A	B	C	D
1	SAMPLE 1
2	SKU Description	Master SKU	Parent/Box	Sales Units
3	Item 1 (Parent)	Item 1	Parent	5
4	Item 1 (Box 1)	Item 1	Box 1	1
5	Item 1 (Box 2)	Item 1	Box 2	2
6	Item 2 (Parent)	Item 2	Parent	20
7	Item 2 (Box 1)	Item 2	Box 1	5
8	Item 2 (Box 2)	Item 2	Box 2	5
9	Item 2 (Box 3)	Item 2	Box 3	6
10
11	Master SKU	Total Sales Units
12	Item 1	6
13	Item 2	25
Sheet16

Cell Formulas
Range	Formula
B12	=SUMPRODUCT((B$3:B$9=A12)*(C$3:C$9={"Parent","Box 1"})*D$3:D$9)

B12 formula copied down.

 

[Aladin Akyurek]

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

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

1​

[/td][td]Table 1 - Raw Data[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

2​

[/td][td]SKU Description[/td][td]Master SKU[/td][td]Parent/Box[/td][td]Sales Units[/td][/tr]
[tr][td]

3​

[/td][td]Item 1 (Parent)[/td][td]Item 1[/td][td]Parent[/td][td]

5​

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

4​

[/td][td]Item 1 (Box 1)[/td][td]Item 1[/td][td]Box[/td][td]

1​

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

5​

[/td][td]Item 1 (Box 2)[/td][td]Item 1[/td][td]Box[/td][td]

2​

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

6​

[/td][td]Item 2 (Parent)[/td][td]Item 2[/td][td]Parent[/td][td]

20​

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

7​

[/td][td]Item 2 (Box 1)[/td][td]Item 2[/td][td]Box[/td][td]

5​

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

8​

[/td][td]Item 2 (Box 2)[/td][td]Item 2[/td][td]Box[/td][td]

5​

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

9​

[/td][td]Item 2 (Box 3)[/td][td]Item 2[/td][td]Box[/td][td]

6​

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

10​

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

11​

[/td][td]Table 2 - Total Sales by Master SKU[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

12​

[/td][td]Master SKU[/td][td]Total Sales Units[/td][td][/td][td][/td][/tr]
[tr][td]

13​

[/td][td]Item 1[/td][td]

6​

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

14​

[/td][td]Item 2[/td][td]

25​

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

In B13 enter and copy down:

Either...

=SUM(SUMIFS($D$3:$D$9,$B$3:$B$9,$A13,$C$3:$C$9,"parent"),MINIFS($D$3:$D$9,$B$3:$B$9,$A13,$C$3:$C$9,"box"))

Or...

=SUM(VLOOKUP($A13&"*parent*",$A$3:$D$9,4,0),MINIFS($D$3:$D$9,$B$3:$B$9,$A13,$C$3:$C$9,"box"))

The foregoing requires that MINIFS is available on your system.

Otherwise control+shift+enter, not just enter, and copy down:

Or...

=SUM(SUMIFS($D$3:$D$9,$B$3:$B$9,$A13,$C$3:$C$9,"parent"),MIN(IF($B$3:$B$9=$A13,IF($C$3:$C$9="box",$D$3:$D$9))))

Or...

=SUM(VLOOKUP($A13&"*parent*",$A$3:$D$9,4,0),MIN(IF($B$3:$B$9=$A13,IF($C$3:$C$9="box",$D$3:$D$9))))

 

[l_doherty]

Hi,

Thanks so much to both of you for posting solutions.

The one that I found worked the best was =SUM(SUMIFS($D$3:$D$9,$B$3:$B$9,$A13,$C$3:$C$9,"parent"),MIN(IF($B$3:$B$9=$A13,IF($C$3:$C$9="box",$D$3:$D$9))))

 

[Aladin Akyurek]

@ l_doherty

You are welcome. Thanks for the update.

 

[markmzz]

Hi Guys,

I need some help finding a formula that will give me the total sales units of an item where I need to add the sales units of the "Parent" and the minimum sales units of the "Box" that relate to the item.

For example below, Item 1 is the sum of Item 1 (Parent) + minimum of (Item 1 Box 1) and (Item 1 Box 2), therefore 5 + 1 = 6.

I would like to display the totals by Master SKU in a separate table, as shown in Table 2.

Is there a forumula where I can use the "Master SKU" column and the "Parent/Box" column that will give me the correct answer?

Thanks in advance for your help!

Hi!

Another way (if possible):

Use only Enter to enter the formula

=SUM(AGGREGATE({14;15},6,$D$3:$D$9/(B$3:B$9=A16),1))

Markmzz