Complex sumproduct to return unique values

[krisLB7]

Having an issue creating a sumproduct for a dashboard (Table 1) that returns the number of unique users by number of visits. I'm trying to create a formula that calculates Col_D of Table 1 without using an helper formulas.

The formula first filters by date (month).

Table 1/

A	B	C	D
	Aug	Sept	Oct
Users (by visits)			3​
5+
4-5			1​
2-3			1​
1			1​

Table 2/

A	B	C	D	E	F	G
Date	Item	Qty	Gross Sales	Transaction ID	Dining Option	Customer ID
10/31/2020​	Item1	1​	$2.00​	Trans1_uid	For Here	customerA
10/31/2020​	Item2	1​	$2.00​	Trans1_uid	For Here	customerA
10/31/2020​	Item3	1​	$2.00​	Trans2_uid	For Here	customerA
10/31/2020​	Item4	1​	$2.00​	Trans2_uid	For Here	customerA
10/31/2020​	Item5	1​	$2.00​	Trans2_uid	For Here	customerA
10/31/2020​	Item6	2​	$2.00​	Trans3_uid	For Here	customerA
10/31/2020​	Item7	1​	$2.00​	Trans4_uid	For Here	customerB
10/31/2020​	Item8	1​	$2.00​	Trans4_uid	For Here	customerB
10/31/2020​	Item9	1​	$2.00​	Trans5_uid	For Here	customerB
10/31/2020​	Item10	1​	$2.00​	Trans6_uid	For Here	customerB
10/31/2020​	Item11	1​	$2.00​	Trans7_uid	To Go	customerB
10/31/2020​	Item12	1​	$2.00​	Trans7_uid	To Go	customerB
10/31/2020​	Item13	1​	$2.00​	Trans7_uid	To Go	customerB
10/31/2020​	Item14	1​	$2.00​	Trans7_uid	To Go	customerB
10/31/2020​	Item15	1​	$2.00​	Trans8_uid	To Go	customerC

 

[mart37]

So you can filter with sumproduct: (range=criterion)

 

[krisLB7]

So you can filter with sumproduct: (range=criterion)

I'm not exactly sure what you're asking.

My comment about "the formula first filters by date" was unnecessary. I was simply explaining how I need to populate Table 1 (which is organized by date(month)). So was just pointing that out. I don't need to actively filter and/ or adjust the data. I was pointing out that the first two items in my formula: SUMPRODUCT(--(A:A>="10/1/2020"), --(A:A<="10/31/2020"), .......).

My trouble is that it is nesting Array formulas into an Array formula - at least that is my approach on how to solve it.

The date entered above is just a small sample of data. This is operating on quite a large population of data.

 

[CluelessAccountant]

I'm not sure exactly what the desired output is, but specifically in terms of filtering by date range with sumproduct:

=SUMPRODUCT((Column of output you want returned)*(A:A>=DATE(2020,10,01))*(A:A<=DATE(2020,10,31))) OR you could do something like =SUMPRODUCT((Column of output you want returned)*(A:A=MONTH(D$2)))

Depending on what you want, it might make more sense to use a combination of the new-ish FILTER and UNIQUE functions.

Also, if you aren't doing this in the actual file, it might be good to use named ranges or table column names as opposed to the range of the entire column.

 

[krisLB7]

I'm not sure exactly what the desired output is, but specifically in terms of filtering by date range with sumproduct:

=SUMPRODUCT((Column of output you want returned)*(A:A>=DATE(2020,10,01))*(A:A<=DATE(2020,10,31))) OR you could do something like =SUMPRODUCT((Column of output you want returned)*(A:A=MONTH(D$2)))

Depending on what you want, it might make more sense to use a combination of the new-ish FILTER and UNIQUE functions.

Also, if you aren't doing this in the actual file, it might be good to use named ranges or table column names as opposed to the range of the entire column.

I'm trying to fill in the contents of Table_1 listed above using Table_2 as the source data. That's the overall objective.

Using Sumproduct to filter by date, I already do throughout the sheet.