Forum Discussion
Help with formula - Finding adding unique values, with a dollar range, within a date
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 ...
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.
=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?
