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.
A BC1DateEmployee IDAmount2April1122334003April4477226004April112233505April2255881006April3355997007May4477225508May9977442509May55667710010May55667725011May112244800 EXAMPLE OF OUTPUT 500 and below: Date# EmployeesTotal16April255017May2600 501-1000 20April2130021May21350
Using Excel for Mac, version 16.88, License: Microsoft 365, 2024I 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. A BC1DateEmployee IDAmount2April1122334003April4477226004April112233505April2255881006April3355997007May4477225508May9977442509May55667710010May55667725011May112244800 EXAMPLE OF OUTPUT 500 and below: Date# EmployeesTotal16April255017May2600 501-1000 20April2130021May21350 Read More
