Count unique numbers within range

[BrutalLogiC]

help please, I'm trying to write a formula in the cells in yellow which will
1. in column D count the total number of unique POs ("purchasing documents") raised which fall under Group A (cell B5) and are within the range B7 to C7.
2. in column G sum the total of POs raised, it is ok to count the duplicate

The 'purchasing document' is the PO number on the master list sheet. There can be multiple rows for each PO number but I'm only interested in unique POs for the count.

You can see rows 37, 38, 39 and 40 have the same PO number so that will just count as one PO but I still want the total sum of all 4 rows to be included... hope that makes sense.

Book1
	B	C	D	G
5	Group A
6	PO Range From:	PO Range To:	No. of POs raised in calendar year	Sum of POs raised
7	-	20,000
8	20,001	250,000
9	250,001	100,000,000
10
11			0	-
12
13	Group B
14	PO Range From:	PO Range To:	No. of POs raised in calendar year	Sum of POs raised
15	-	150,000
16	150,001	300,000
17	300,001	1,000,000
18	1,000,001	100,000,000
19			0	-
DOA

Book1
	B	C	H
3	Entity	Purchasing Document	Net Order Value
4	Group A	1300008266	96.00
37	Group B	1300008267	84.00
38	Group B	1300008267	168.00
39	Group B	1300008267	165.00
40	Group B	1300008267	165.00
41	Group C	1300008268	9,500.00
42	Group D	1300008269	216.00
81	Group B	1300008271	360.00
82	Group B	1300008271	220.00
83	Group B	1300008271	280.00
84	Group B	1300008271	360.00
85	Group A	1300008272	171.00
86	Group A	1300008272	80.00
87	Group D	1300008273	294.00
16710	Group A	4000000299	1,665.00
16711	Group A	4000000299	1,665.00
16712	Group B	4000000300	35,100.00
16713	Group D	4000000301	5,895.00
Master list

 

[AhoyNC]

Your PO ranges did not seem to match up to your PO's on the master list, so I made some changes in the example below.

Formula in D5 is an array formula and must be entered with CTRL-SHIFT-ENTER. Drag formula down as needed.

Excel Workbook
	A	B	C	D	E
1		Group A
2		PO Range	PO Range	No. of POs raised in calendar year	Sum of POs raised
3
4		From:	To:
5		1000000000	1500000000	2	347
6		1500000001	4000000000	0	0
7		4000000001	5000000001	1	3330
DOA

Excel Workbook
	A	B	C	D
1		Entity	Purchasing Document	Net Order Value
2		Group A	1,300,008,266	96.00
3		Group B	1300008267	84.00
4		Group B	1300008267	168.00
5		Group B	1300008267	165.00
6		Group B	1300008267	165.00
7		Group C	1300008268	9,500.00
8		Group D	1300008269	216.00
9		Group B	1300008271	360.00
10		Group B	1300008271	220.00
11		Group B	1300008271	280.00
12		Group B	1300008271	360.00
13		Group A	1300008272	171.00
14		Group A	1300008272	80.00
15		Group D	1300008273	294.00
16		Group A	4000000299	1,665.00
17		Group A	4000000299	1,665.00
18		Group B	4,000,000,300	35,100.00
19		Group D	4000000301	5,895.00
Master list

 

[BrutalLogiC]

Thanks Ahoy I used:

{=SUM(IF(FREQUENCY(IF('Master list'!$B$4:$B$20000=$B$5,IF('Master list'!$H$4:$H$20000>=$B7,IF('Master list'!$H$4:$H$20000<=$C7,MATCH('Master list'!$C$4:$C$20000,'Master list'!$C$4:$C$20000,0)))),ROW('Master list'!$C$4:$C$20000)-ROW('Master list'!$C$4)+1),1))}

which worked perfect but it is soooo slow on my laptop, I wonder if there is a faster formula to produce same result?

 

[DanteAmor]

To not use a matrix formula, you can create an auxiliary column in the "Master list" sheet to count the unique documents.

(I guess the values ​​in your range from: to: are amounts).


Then on your sheet "Master list" put the following formula in I4 and the copies down.

<table style="font-family:Arial; font-size:12pt; border-style: groove ;border-color:#0000FF;background-color:#fffcf9; color:#000000; "><tr><td ><b>Master list</b></td></tr></table><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:25.66px;" /><col style="width:54.18px;" /><col style="width:134.97px;" /><col style="width:102.65px;" /><col style="width:70.34px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >H</td><td >I</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">Entity</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">Purchasing Document</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">Net Order Value</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">Unique</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td><td >Group A</td><td style="text-align:right; ">1300008266</td><td style="text-align:right; ">96.00</td><td style="text-align:right; ">1</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td > </td><td >Group B</td><td style="text-align:right; ">1300008267</td><td style="text-align:right; ">84.00</td><td style="text-align:right; ">1</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td > </td><td >Group B</td><td style="text-align:right; ">1300008267</td><td style="text-align:right; ">168.00</td><td style="text-align:right; ">0</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td > </td><td >Group B</td><td style="text-align:right; ">1300008267</td><td style="text-align:right; ">165.00</td><td style="text-align:right; ">0</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td > </td><td >Group B</td><td style="text-align:right; ">1300008267</td><td style="text-align:right; ">165.00</td><td style="text-align:right; ">0</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td > </td><td >Group C</td><td style="text-align:right; ">1300008268</td><td style="text-align:right; ">9,500.00</td><td style="text-align:right; ">1</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td > </td><td >Group D</td><td style="text-align:right; ">1300008269</td><td style="text-align:right; ">216.00</td><td style="text-align:right; ">1</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td > </td><td >Group B</td><td style="text-align:right; ">1300008271</td><td style="text-align:right; ">360.00</td><td style="text-align:right; ">1</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td > </td><td >Group B</td><td style="text-align:right; ">1300008271</td><td style="text-align:right; ">220.00</td><td style="text-align:right; ">0</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td > </td><td >Group B</td><td style="text-align:right; ">1300008271</td><td style="text-align:right; ">280.00</td><td style="text-align:right; ">0</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td > </td><td >Group B</td><td style="text-align:right; ">1300008271</td><td style="text-align:right; ">360.00</td><td style="text-align:right; ">0</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td > </td><td >Group A</td><td style="text-align:right; ">1300008272</td><td style="text-align:right; ">171.00</td><td style="text-align:right; ">1</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td > </td><td >Group A</td><td style="text-align:right; ">1300008272</td><td style="text-align:right; ">80.00</td><td style="text-align:right; ">0</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td > </td><td >Group D</td><td style="text-align:right; ">1300008273</td><td style="text-align:right; ">294.00</td><td style="text-align:right; ">1</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td > </td><td >Group A</td><td style="text-align:right; ">4000000299</td><td style="text-align:right; ">1,665.00</td><td style="text-align:right; ">1</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td > </td><td >Group A</td><td style="text-align:right; ">4000000299</td><td style="text-align:right; ">1,665.00</td><td style="text-align:right; ">0</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td > </td><td >Group B</td><td style="text-align:right; ">4000000300</td><td style="text-align:right; ">35,100.00</td><td style="text-align:right; ">1</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >21</td><td > </td><td >Group D</td><td style="text-align:right; ">4000000301</td><td style="text-align:right; ">5,895.00</td><td style="text-align:right; ">1</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >I4</td><td >=IF(COUNTIFS($B$4:B4,B4,$C$4:C4,C4)=1,1,0)</td></tr></table></td></tr></table> <br /><br />


<table style="font-family:Arial; font-size:12pt; border-style: groove ;border-color:#0000FF;background-color:#fffcf9; color:#000000; "><tr><td ><b>sheet DOA</b></td></tr></table><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:38.02px;" /><col style="width:69.39px;" /><col style="width:84.59px;" /><col style="width:227.17px;" /><col style="width:140.67px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td > </td><td style="background-color:#ffc000; color:#333333; font-weight:bold; font-family:Verdana; font-size:9pt; ">Group A</td><td style="background-color:#ffc000; color:#333333; font-family:Verdana; font-size:9pt; "> </td><td style="background-color:#ffc000; color:#333333; font-family:Verdana; font-size:9pt; "> </td><td style="background-color:#ffc000; color:#333333; font-family:Verdana; font-size:9pt; "> </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td > </td><td style="background-color:#ffc000; color:#333333; font-family:Verdana; font-size:9pt; ">PO Range</td><td style="background-color:#ffc000; color:#333333; font-family:Verdana; font-size:9pt; ">PO Range</td><td style="background-color:#ffc000; color:#333333; font-family:Verdana; font-size:9pt; ">No. of POs raised in calendar year</td><td style="background-color:#ffc000; color:#333333; font-family:Verdana; font-size:9pt; ">Sum of POs raised</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td > </td><td style="color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">0</td><td style="color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">20,000</td><td style="background-color:#ffff00; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">3</td><td style="background-color:#ffff00; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">3,677.00</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td > </td><td style="color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">20,001</td><td style="color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">250,000</td><td style="background-color:#ffff00; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">0</td><td style="background-color:#ffff00; color:#333333; font-family:Verdana; font-size:9pt; "> </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td > </td><td style="color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">250,001</td><td style="color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">100,000,000</td><td style="background-color:#ffff00; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">0</td><td style="background-color:#ffff00; color:#333333; font-family:Verdana; font-size:9pt; "> </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td > </td><td style="color:#333333; font-family:Verdana; font-size:9pt; "> </td><td style="color:#333333; font-family:Verdana; font-size:9pt; "> </td><td style="background-color:#ffff00; color:#333333; font-family:Verdana; font-size:9pt; "> </td><td style="background-color:#ffff00; color:#333333; font-family:Verdana; font-size:9pt; "> </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td > </td><td style="background-color:#d9d9d9; color:#333333; font-family:Verdana; font-size:9pt; "> </td><td style="background-color:#d9d9d9; color:#333333; font-family:Verdana; font-size:9pt; "> </td><td style="background-color:#d9d9d9; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">0</td><td style="background-color:#d9d9d9; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">-</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >D7</td><td >=COUNTIFS('Master list'!$B$4:$B$21,B5,'Master list'!$H$4:$H$21,">="&$B7,'Master list'!$H$4:$H$21,"<="&$C7,'Master list'!$I$4:$I$21,1)</td></tr><tr><td >E7</td><td >=SUMIFS('Master list'!$H$4:$H$21,'Master list'!$B$4:$B$21,$B$5,'Master list'!$H$4:$H$21,">=" & $B7,'Master list'!$H$4:$H$21,"<="&C7)</td></tr></table></td></tr></table> <br /><br />