Forum Discussion

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

Silver Contributor

Apr 29, 2022

Shivashimladka

A solution is to make sure you have access to Excel 365 and write the formula

= LET(
    date,  UNIQUE(sourceDate),
    count, COUNTIFS(sourceDate,date),
    HSTACK(date, count)
  )

PeterBartholomew1

Silver Contributor

Apr 29, 2022

Sorry, misread the question.

= LET(
    distinctInv, UNIQUE(sourceData),
    invDate,     TAKE(distinctInv, ,1),
    date,        UNIQUE(sourceDate),
    count,       MAP(date, LAMBDA(d, 
                     COUNT(IF(invDate=d, 1)))),
    HSTACK(date, count)
  )