Sum If + Count Unique + Multiple Criteria

[natheplas]

Hi,

I'm trying to get my head around a formula and I can't get there.

I have a list of sales orders, a list of despatch numbers, a list of products and a list of qtys.

I need a formula or formulas to look at the list of sales orders, if it appears more than once, look at the despatch numbers,
if there is more than one unique despatch number, add that product qty together.
I've given an example below. I then imagined on a different table, Id have a list of all product codes with the totals next to them.

Hugely appreciate if anyone can help me with this, thank you.

SO1000 DN1234 PROD1 10
SO1000 DN1234 PROD2 10
SO1000 DN1235 PROD1 10
SO1001 DN1236 PROD3 10
SO1001 DN1236 PROD1 10
SO1001 DN1237 PROD2 10
SO1002 DN1238 PROD3 10
SO1003 DN1239 PROD1 10


PROD1 10
PROD2 10
PROD3 0

 

[natheplas]

Re: Sum If + Count Unique + Multiple Criteria Please Help

Hi Guys,

Can I add anything more to get help on this please?
Really stuck, appreciate someone's help with it.

 

[Eric W]

Re: Sum If + Count Unique + Multiple Criteria Please Help

I've looked at this, and for the life of me, I can't figure out what you're asking. Probably why you haven't gotten any responses. Is your first table the raw data, and the second your expected results? If so, why 10 for PROD1? Why 10 for PROD2? Why 0 for PROD3? Do the underlines in table1 mean anything?

 

[natheplas]

Re: Sum If + Count Unique + Multiple Criteria Please Help

Hi Eric,

Sorry!

Yes, first table raw data and second table expected results.

To explain in more detail:

So the first column is a list of sales orders. The second column is despatch note numbers, (Deliveries).

If we look at the below data: There is one Sales Order = SO1000 and two despatch numbers (deliveries) = DN1234, DN1235.

Then you have the product column. PROD1 and PROD2 = Product 1 and Product 2. Then Quantities despatched = 10.

So the below data tells me that, 10 * PROD1 and 10 * PROD2 went on the first delivery (DN1234), for Sales order 1000 (S01000).

I would like a table to record totals of second or more deliveries.

So in the below example, on the second delivery (DN1235), 10 * PROD1 were despatched.

I would like a table that records second or more deliveries.

So just from the data set below the desired result would be PROD1 = 10

SO1000 DN1234 PROD1 10
SO1000 DN1234 PROD2 10
SO1000 DN1235 PROD1 10

 

[Eric W]

Re: Sum If + Count Unique + Multiple Criteria Please Help

So if you had an additional line on that table of

SO1000 DN1236 PROD1 5

You'd want an answer of 15?

What if on your original table you had another line of

SO1003 DN1240 PROD1 8

would you want an answer of 18 for PROD1 (3rd line from the table + this line), or would you want 2 output lines:

SO1000 PROD1 10
SO1003 PROD1 8

?

What if you had a table like

SO1000 DN1234 PROD2 5
SO1000 DN1235 PROD1 10

Would you want the PROD1 amount included in the total since it wasn't in the original delivery?

 

[natheplas]

Re: Sum If + Count Unique + Multiple Criteria Please Help

Yes an answer of 15.

I'd want an answer of 18 for PROD1.

Yes, PROD1 amount included in the total since it wasn't in the original delivery.

Thank you for working on this one with me.

 

[Eric W]

Re: Sum If + Count Unique + Multiple Criteria Please Help

The only way I've figured out how to do it so far is with a helper column:

Excel 2012

	A	B	C	D	E	F	G	H
Sales	Despatch	Product	Amount	Helper	Product	Amount in later Despatches
SO1000	DN1234	PROD1	PROD1
SO1000	DN1234	PROD2	PROD2
SO1000	DN1235	PROD1	PROD3
SO1001	DN1236	PROD3
SO1001	DN1236	PROD1
SO1001	DN1237	PROD2
SO1002	DN1238	PROD3
SO1003	DN1239	PROD1

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]

[TD="align: right"]10[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]

[TD="align: right"]10[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H2[/TH]
[TD="align: left"]=SUMPRODUCT($E$2:$E$15,--($C$2:$C$15=G2),$D$2:$D$15)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]=IFERROR(IF(B2<>INDEX($B$1:$B2,MATCH(A2,$A$1:$A2,0)),1,0),0)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



There's probably a way to do it without the helper column, but it would be quite tricky. I'll think about it a bit more.

 

[natheplas]

Re: Sum If + Count Unique + Multiple Criteria Please Help

Hi Eric,

That's perfect. Thank you so much for your help with this one. With helper is fine.

 

[Eric W]

Re: Sum If + Count Unique + Multiple Criteria Please Help

Glad to help.