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