excelpivot-table

Calculate percentage of rows containing a string, grouped by month

I would like to calculate the percentage of invoices that contain the word "bend", grouped by month. I have the following sample data in Excel:

Date Invoice Number Line Item
08/01/2024 100 Cut material
08/01/2024 100 Cut and bend
09/01/2024 200 Part ABC
09/01/2024 201 Part DEF
10/01/2024 300 Cut and bend
10/01/2024 300 Bend parts
10/03/2024 301 Cut only

The resulting output should be something like:

Month Percent with bend
08/2024 100%
09/2024 0%
10/2024 50%

I could add a column searching for the word "bend", and then create a PivotTable to get unique Invoice Numbers and whether or not any of their Line Items contain "bend". However, I don't know how to use this to calculate the percent of invoice numbers containing bend grouped by month.

Solution

  • I chose to put the unique months in F2 using a separate formula:

    =UNIQUE(DATE(YEAR(A2:A8),MONTH(A2:A8),1))

    And the main formula in G2:

    =LET(key,E2,month,F2#,date,DATE(YEAR(A2:A8),MONTH(A2:A8),1),invoice,B2:B8,Line,C2:C8,
    MAP(month,LAMBDA(c,IFERROR(ROWS(UNIQUE(FILTER(invoice,(date=c)*ISNUMBER(SEARCH(key,Line))))),0)*100/ROWS(UNIQUE(FILTER(invoice,date=c))))))

    enter image description here

    Or if you prefer a single formula:

    =LET(key,E2,month,F2#,date,DATE(YEAR(A2:A8),MONTH(A2:A8),1),invoice,B2:B8,Line,C2:C8,
  HSTACK(UNIQUE(date),
    MAP(month,LAMBDA(c,IFERROR(ROWS(UNIQUE(FILTER(invoice,(date=c)*ISNUMBER(SEARCH(key,Line))))),0)*100
      /ROWS(UNIQUE(FILTER(invoice,date=c)))))))