Unique(Filter( criteria between values help

Question

Greetings,

I am trying to make a macro to speed up my operations, but I am hitting a wall min my formula knowledge and can't figure out what key words track down the info I want.

Short version: I want to find the sum of Unique Items w/ their unique locations filtered by their department

I can get data if I use =Unique(Filter(B:C,A:A<3000)) for the smaller department range
however if I try using =Unique(Filter(B:C,3000

Example Data Set below

Department	Item Number	Location	Sum
2000	555	75b6	5
2010	555	75b6	5
2030	555	75b6	10
2050	556	77b3	5
2080	555	75b6	10
22100	632	66b1	6
2030	554	77b4	15
3001	114	55b9	4
3030	114	55b9	8
33300	800	55b3	2

Results from the formulas

=UNIQUE(FILTER(B:C,A:A<3000))	=UNIQUE(FILTER(B:C,3000
555	75b6	30	#CALC!
556	77b3	5
554	77b4	15
0	0	0

With Good Fortune,
Pirate

Answer 1

Hi,

In cell F4, enter this formula

=GROUPBY(B4:C13,D4:D13,SUM,,0,,((A4:A13>=G1)*(A4:A13<=G2)))

Hope this helps.

Answer 2

This is probably not what you want, but as a side note. It is very easy as a dynamic worksheet function.

Expanding in the horizontal direction however requires another step.

Answer 3

While that is an option, its not exactly what I need. Using a pivot table, it is looking at all of the departments (it just needs to be 2000-3000, 3000-4000, etc), not every Department and sub-department

Is there a way to have it filter by the range for the Department to it results in something closer to

You can set a filter in a Pivot table. A comfortable way is to use slicers.

Use slicers to filter data - Office Support

Andreas.

Answer 4

Greetings,

While that is an option, its not exactly what I need. Using a pivot table, it is looking at all of the departments (it just needs to be 2000-3000, 3000-4000, etc), not every Department and sub-department

Is there a way to have it filter by the range for the Department to it results in something closer to

Department 2 (2000-3000)	Department 3 (3000-4000)	Department 22 (22000-33000)
555	75b6	30	114	55b9	12	632	66b1	6
556	77b3	5
554	77b4	15

Answer 5

Use a pivot table.

Create a PivotTable to analyze worksheet data - Office Support

Drag "Item Number" and Location into the rows section, Sum into the values section, done.

Andreas.