Forum Discussion

Shivashimladka's avatar
Shivashimladka
Copper Contributor
Apr 29, 2022

How to Count of invoice numbers in a column

How do we count date wise total invoices from the below table (without counting duplicates)   I have tried this formula =SUM(IF(FREQUENCY(C14:C24,C14:C24)>0,1)) but this gives total counts (excl du...
excel
Formulas and Functions
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Apr 30, 2022

Shivashimladka 

I have come to the conclusion that building the entire array of dates and counts in one formula overcomplicates things.  So, given the distinct dates in a spilt range

= MAP(distinctDate#,
      LAMBDA(d, COUNTA(
          UNIQUE(FILTER(invoice, invoiceDate=d))
      ))
  )
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Apr 30, 2022

    SergeiBaklan HansVogelaar 

    Agreed that it is a good idea to demonstrate that the Pivot Table provides a realistic solution, and without requiring knowledge of formulas.  The tabular format without subtotals is reasonably easy to emulate using array formulas but to include subtotals or table pivoting requires serious effort without the Pivot Table.  I always get caught out by the need to refresh but, then, I haven't used Calculation Manual either.

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Apr 30, 2022

      PeterBartholomew1 

      I believe one day we will have out of the box DA formula for the aggregations. It's hard to compete with PowerPivot which has tens of years history, nonetheless. All depends on concrete case. Cube formulae in combination with dynamic arrays could be a compromise for today, but here we shall train our brains with MDX to generate more or less complex solution.

      To refresh or not to refresh is always the question.

Resources