Forum Discussion
Countifs
9 Replies
This only applies to Excel 365.
= LET( distinctSalesManager, UNIQUE(SalesManager), distinctSalesPerson, UNIQUE(SalesPerson), totalSales, SUMIFS(SalesAmount, SalesPerson, distinctSalesPerson), qualifies, SIGN(totalSales>=Threshold), personsManager, XLOOKUP(distinctSalesPerson, SalesPerson, SalesManager), matrix, SIGN(TRANSPOSE(personsManager) = distinctSalesManager), CHOOSE({1,2}, distinctSalesManager, MMULT(matrix, qualifies)))It calculates the total sales for each sales person, then build a matrix to show which sales manager the sales person reports to. Matrix multiplication is then used to count the number of salesmen managed by the given sales manager have met the threshold sales value.
- This is gnarly, but isn't it basically just rebuilding a pivot table?
I don't think so. The SUMIFS function with arrays for the criteria fields can replicate the values one would obtain with a pivot table, but I don't think the calculation is executed by the same code. Since I use Excel 365 Insider beta channel, I have access to new functions that allow solutions to depart even further from the norms of traditional spreadsheet.
= MAP(UNIQUE(SalesManager), LAMBDA(mgr, LET( teamMember, UNIQUE(FILTER(SalesPerson, SalesManager=mgr)), teamSalesAmount, SUMIFS(SalesAmount, SalesPerson, teamMember), SUM(SIGN(teamSalesAmount > Threshold)) ) ) )That is:
- for each distinct sales manager, filter the sales person list to return the managers team;
- for each team member, calculate their total sales to date;
- count the number that exceed the cut-off value.
The greatest challenge might be even recognising that the offered solution is an Excel worksheet formula!
- This would probably best be done in a pivot table. If you take the data, turn it into a pivot and use agents as row field and sales $'s as values, it'll list each agent and their total sales. Then you can right click the sales totals > sort by largest to smallest and easily see who did over 25k.
More info on your data and workbook would be helpful!
