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

SKU Description	Master SKU	Parent/Box	Sales Units
Item 1 (Parent)	Item 1	Parent	5
Item 1 (Box 1)	Item 1	Box	1
Item 1 (Box 2)	Item 1	Box	2
Item 2 (Parent)	Item 2	Parent	20
Item 2 (Box 1)	Item 2	Box	5
Item 2 (Box 2)	Item 2	Box	5
Item 2 (Box 3)	Item 2	Box	6

<tbody>
</tbody>

Table 2 - Total Sales by Master SKU

Master SKU	Total Sales Units
Item 1	6
Item 2	25

<tbody>
</tbody>

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]

Row\Col	A​	B​	C​	D​
1​	Table 1 - Raw Data
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​
5​	Item 1 (Box 2)	Item 1	Box	2​
6​	Item 2 (Parent)	Item 2	Parent	20​
7​	Item 2 (Box 1)	Item 2	Box	5​
8​	Item 2 (Box 2)	Item 2	Box	5​
9​	Item 2 (Box 3)	Item 2	Box	6​
10​
11​	Table 2 - Total Sales by Master SKU
12​	Master SKU	Total Sales Units
13​	Item 1	6​
14​	Item 2	25​

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