Countifs

Copper Contributor
In the sheet there r 10 sales managers and they have 15 agents. Agents have done business whose amounts are mentioned. Question is- Need SM wise, how many agents have done business over 25k till date. What shld be the countifs formula?
9 Replies
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!

@Chirantan_77 

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?

@DKoontz 

PivotTable requires refresh, from that point of view formulas is always better. But if go this way

- load by Power Query named cell Threshold to data model;

- creating PivotTable add data to data model;

- couple of measures

sumAmount:=SUM ( Range[Sales Amount] )

Top Staff:=SUMX (
    VALUES ( Range[Sales Person] ),
    INT ( [sumAmount] > VALUES ( Threshold[Column1] ) )
)

second one into PivotTable:

image.png

@DKoontz 

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!

 

@Peter Bartholomew 

Never think about that, but logic behind is close (yes, sure, that's not the mapping)

 

- MAP() creates rows content as if in PivotTable we put SalesManager into rows;

- SUM([Sales Amount] in current content works as SUMIFS()

- VALUES([Sales Person]) is UNIQUE(SalesPerson) for current row content;

- SUMX() iterates it (again, for current content) and sums number of condition met.

@Peter Bartholomew 

This is seriously so cool, way beyond my current scope, I don't have MAP or LAMBDA access with my current subscription. Where did you guys (@Sergei Baklan too) learn this stuff? I'm always trying to get better with excel and you've blown my mind with building these custom functions and matrixes.

 

Totally next level stuff.

@DKoontz 

Lambdas are available for Office Insiders with 365 subscription on Beta and now Current (Preview) channels.

Supporting functions like MAP() are only on Beta channel.

Brief description is in blogs here in Excel community, plus on Office Insider site in blogs, plus support pages (e.g. google "excel lambda scan"), plus on different forums includes this one.

@DKoontz 

The primary source of information is the blog announcements (as an MVP @Sergei Baklan presumably gets more in the way of previews).

Basic arrays:

Microsoft Excel: Advanced spreadsheet modeling usi... - Power Platform Community

The LET function:

Announcing LET in Excel (microsoft.com)

The LAMBDA function:

Announcing LAMBDA (microsoft.com)

LAMBDA helper functions (these make recursive calculation more straightforward)

Announcing LAMBDA Helper Functions (microsoft.com)

The next thing is to gain access to an Excel 365 license that you have the authority to switch to the Insider beta channel and then start practicing and developing new techniques.