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 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 BC
1DateEmployee IDAmount
2April112233400
3April447722600
4April11223350
5April225588100
6April335599700
7May447722550
8May997744250
9May556677100
10May556677250
11May112244800
    
 EXAMPLE OF OUTPUT 
 500 and below: 
 Date# EmployeesTotal
16April2550
17May2600
    
 501-1000  
20April21300
21May21350

 

excel
Excel on Mac
Formulas and Functions

4 Replies

Sort By
  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Sep 13, 2024

    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)
)
  • 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