Forum Discussion

Shivashimladka's avatar
Shivashimladka
Copper Contributor
Apr 28, 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
OliverScheurich's avatar
OliverScheurich
Gold Contributor
Apr 29, 2022

Shivashimladka 

The array of dates and invoice numbers has 11 rows. If you select the MATCH or the ROW part of the formula and press F9 the results are shown in the formula bar. You can see them in the pictures in the attached file. The "then" part of the IF formula compares these results. You can return to the formula with ctrl+Z.

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Apr 29, 2022

OliverScheurich 

This is my way of understanding your approach.  Range references always were meaningless to me so refactoring was a key element of my understanding a formula.  I never could tell whether the result was more intelligible or whether it was simply that the refactoring process provided insight.

= LET(
    recordNum,       SEQUENCE(ROWS(sourceData)),
    selectedDate?,   sourceDate=@date,
    selectedInvoice, IF(selectedDate?,invoice),
    firstMatch,      MATCH(selectedInvoice, selectedInvoice,),
  SUM(N(IF(selectedDate?, firstMatch=recordNum))))

It wouldn't have been quite like this a year or two ago, but LET and LAMBDA are now so deeply engrained in my thought process, I almost forget how things used to be.

Resources