Help with formula - Finding adding unique values, with a dollar range, within a date

[strange4765]

Using Excel for Mac, version 16.88, License: Microsoft 365, 2024

I have a large spreadsheet (approx 30,000 rows).  

1) I need search by Date (A) to find transactions in April.  

2) Then I need to search Employee ID (B) to find how many unique employees had a transaction, and add Amount (C) for every unique user.

3) I then need to record them in the correct data set, such as Under 500 or 500-1000.

So in the below example, I wanted to find how many employees spend 500 or less in April.  I see that Employee 112233 has 2 transactions in April, so I need to add C2 and C4 together.  The total is less than 500, so I will include them  in the calculation in B16 (# of employees) and C16 (Amount).  I would also add B5 & C5 to the April 500 & Under total.   However, it would not add the data from Row 6 since the totals for those transactions are over 500 in April.

I have found the formula to take care of B16-21.  Just can't find formula to include with a text date (A) and Sum the amounts in C.

	A	B	C
1	Date	Employee ID	Amount
2	April	112233	400
3	April	447722	600
4	April	112233	50
5	April	225588	100
6	April	335599	700
7	May	447722	550
8	May	997744	250
9	May	556677	100
10	May	556677	250
11	May	112244	800
	EXAMPLE OF OUTPUT
	500 and below:
	Date	# Employees	Total
16	April	2	550
17	May	2	600
	501-1000
20	April	2	1300
21	May	2	1350

[#2]

@strange4765 Use the FILTER function in combination with UNIQUE and COUNT for the count of employees. FILTER and SUM will do for the total amount.

I've simplified it a bit by assuming count/sum for amounts up to 500 and above 500. If you really need to excluded amount over 1000, I trust you can expand the formulas based on the examples in the attached file.

[#3]

This was very helpful to calculate the count in B16, however, the formula doesn't work for C16. For C16 should only include calculations for the result of B16. For example, change C4 above to 400. Once that change is made, the SUM of C2 + C4 (800) should exclude this employee from being included in the calculation in B16 and C16.

[#4]

@strange4765 

 =LET(months,A2:A11,

employee,B2:B11,

amount,C2:C11,

IFNA(VSTACK("500 and below",

HSTACK(UNIQUE(months),

DROP(REDUCE("",UNIQUE(months),LAMBDA(u,v,VSTACK(u,LET(

z,UNIQUE(FILTER(employee,months=v)),

y,BYROW(z,LAMBDA(r,SUM(FILTER(amount,(employee=r)*(months=v))))),

x,HSTACK(z,y),

IFERROR(HSTACK(COUNTA(UNIQUE(FILTER(z,y<=500))),SUM(UNIQUE(FILTER(y,y<=500)))),""))))),1)),

"",

"501-1000",

HSTACK(UNIQUE(months),

DROP(REDUCE("",UNIQUE(months),LAMBDA(u,v,VSTACK(u,LET(

z,UNIQUE(FILTER(employee,months=v)),

y,BYROW(z,LAMBDA(r,SUM(FILTER(amount,(employee=r)*(months=v))))),

x,HSTACK(z,y),

IFERROR(HSTACK(COUNTA(UNIQUE(FILTER(z,(y>500)*(y<=1000)))),SUM(UNIQUE(FILTER(y,(y>500)*(y<=1000))))),""))))),

1)))

,"")

)

Does this formula return the intended result in the sample file?

[#5]

@strange4765 

If you have access to GROUPBY, you could use this formula:

=LET(
    less_than_or_equal_500, Demo[Amount] <= 500,
    greater_than_500, Demo[Amount] > 500,
    fn, HSTACK(COUNT, SUM),
    a, GROUPBY(Demo[Date], Demo[Amount], fn, , 0, , less_than_or_equal_500),
    b, GROUPBY(Demo[Date], Demo[Amount], fn, , 0, , greater_than_500),
    VSTACK(a, b)
)