Forum Discussion

strange4765's avatar
strange4765
Copper Contributor
Sep 12, 2024

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 ...
excel
Excel on Mac
Formulas and Functions
Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Sep 13, 2024

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.

 

strange4765's avatar
strange4765
Copper Contributor
Sep 13, 2024
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.
  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Sep 13, 2024

    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?

Resources