count unique based on criteria in pivot table

[Sharksfan]

I have a pivot table in which I need to count unique locations based on inventory program, but not include locations where the sum of available field is equal to 0 - zero. I have uploaded a mini-spreadsheet with data.

DCH Inventory Management.xlsx
	A	B	C	D	E
1	Row Labels
2	Allocation - DD Stage
3	DCH Hold
4	Overage
5	Showa Gloves
6	Smart Source Gloves
7	(blank)
8	Location	Part number	UOM	Inventory Program	Sum of Available
9	DT40A1	AT73035	CS	Allocation - DD Stage	29
10	DT40A2	88TN04L	CS	Allocation - DD Stage	0
11	DT40B1	88TN02S	CS	Allocation - DD Stage	0
12	DT40B1	CH6G0048	CS	Allocation - DD Stage	0
13	DT40B1	88RTF04L	CS	Allocation - DD Stage	34
14	DT40B2	9444	CS	Allocation - DD Stage	33
15	DT40B2	8856NMB	CS	Allocation - DD Stage	39
16	DT40C1	9444	CS	Allocation - DD Stage	16
17	DT40C2	8486	CS	Allocation - DD Stage	50
18	DT40D1	8486	CS	Allocation - DD Stage	41
19	DT40D2	7101PG	CS	Allocation - DD Stage	17
20	DT41A1	7101PG	CS	Allocation - DD Stage	18
21	DT41A2	8210	CS	Allocation - DD Stage	40
22	DT41B1	88ES02S	CS	Allocation - DD Stage	19
23	DT41B2	29408	CS	Allocation - DD Stage	12
24	DT41B2	29408	CS	Overage	5
25	DT41C1	29408	CS	Allocation - DD Stage	15
26	DT41C2	8210	CS	Allocation - DD Stage	39
27	DT41D1	88TN04L	CS	Allocation - DD Stage	0
28	DT41D2	29408	CS	Allocation - DD Stage	20
29	DT42A1	88TN02S	CS	Allocation - DD Stage	33
30	DT42A1	88TN03M	CS	Allocation - DD Stage	0
31	DT42A2	8858NXLB	CS	Allocation - DD Stage	25
32	DT42A2	8858NXLB	CS	DCH Hold	10
33	DT42A2	88TN04L	CS	Allocation - DD Stage	0
34	DT42B1	8887DOTP	CS	Allocation - DD Stage	15
35	DT42B2	29408	CS	Allocation - DD Stage	15
36	DT42C1	8886DOTP	CS	Allocation - DD Stage	16
37	DT42C2	88TN05XL	CS	Allocation - DD Stage	15
Inventory by Program

 

[RasGhul]

Maybe try

=COUNTA(UNIQUE(FILTER($A$9:$A$37,$E$9:$E$37>0)))

 

[Sharksfan]

Maybe try

=COUNTA(UNIQUE(FILTER($A$9:$A$37,$E$9:$E$37>0)))

I had the COUNTA and UNIQUE functions, but I did not realize there was a Filter function. After learning about the FILTER function, I learned you can filter on multiple criteria in which case I was able to also filter by "inventory program" to get what I needed. Thank you for pointing me in the right direction! complete formula is =COUNTA(UNIQUE(FILTER($A$9:$A$37,($E$9:$E$37>0)*($D$9:$D$37=A2),0))) in cell B2. Then copy down to cell B6.

 

[RasGhul]

Thanks for the feedback