Formula to check if data is a bulk delivery

[Prinny]

Hi all, hoping you can help as I'm a bit lost with this.
My data has several columns of information regarding deliveries, the dates they were made and if all the items ordered by the customer were in the delivery.

I need a formula to check the data and put a value of "bulk" if a delivery with multiple items was made to the same company on the same date. It should only say "bulk" once for each delivery so I can later count the number of bulk deliveries.

If a delivery was made with only one item, it should not be counted.

Ultimately, I am trying to work out how many "bulk" deliveries were made in a timeframe and then how many of the ordered items in that delivery were sent or not sent.

Data would look like:

Date / company name / item name / item sent? / bulk check
01.01.2019 / company A / oranges / no / bulk
01.01.2019 / company A / apples / yes / (blank)
01.01.2019 / company A / peaches / yes / (blank)
02.02.2019 / company B / oranges / yes / (blank)
03.03.2019 / company A / oranges / yes / bulk
03.03.2019 / company A / peaches / no/ (blank)

I need the formula to show in the last column that the delivery on 01.01.2019 was a bulk order (counted only once)

The delivery on 02.02.2019 is not a bulk order as it is only one item.
The delivery on 03.03.2019 is a bulk order.

After that, I will try to find a way to look at each bulk order and tell me how many items were ordered (number of rows for that delivery), how many were delivered and how many were not delivered (yes or no in column D)

I might be going about this the wrong way so would be grateful of any advice.

I am working in Excel 2010.
Thanks

 

[Eric W]

Try:

Excel 2012

	A	B	C	D	E	F	G	H
Date	Company Name	Item Name	Item sent?	Bulk check	# ordered	# delivered	# not delivered
A	oranges	no	bulk
A	apples	yes
A	peaches	yes
B	oranges	yes
A	oranges	yes	bulk
A	peaches	no

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

[TD="align: center"]2[/TD]
[TD="align: right"]1-Jan[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1-Jan[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]1-Jan[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]2-Feb[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]3-Mar[/TD]

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

[TD="align: center"]7[/TD]
[TD="align: right"]3-Mar[/TD]

</tbody>

Sheet5​

[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] "]E2[/TH]
[TD="align: left"]=IF(COUNTIFS($A$2:$A2,A2,$B$2:$B2,B2)=1,IF(COUNTIFS(A:A,A2,B:B,B2)>1,"bulk",""),"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]=IF(E2="bulk",COUNTIFS(A:A,A2,B:B,B2),"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G2[/TH]
[TD="align: left"]=IF(E2="bulk",COUNTIFS(A:A,A2,B:B,B2,D:D,"yes"),"")[/TD]
[/TR]
[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"]=IF(E2="bulk",F2-G2,"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Prinny]

Hi Eric,

Thank you so much for posting this brilliant solution. It works perfectly, exactly what I needed.

I was trying lots of complicated "if a2 is the same as a3 but not the same as a1" type formulas and just working myself into knots. I would never have thought of using Countifs as a condition. I think i'll look it up and learn it for future use!

Thank you very much for your help

 

[Eric W]

COUNTIFS is definitely a function worth knowing!

Glad I could help.