# How to Count of invoice numbers in a column

Copper Contributor

# 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 duplicates) irrespective of date

 Date Inv Number 24-04-2022 1 24-04-2022 2 24-04-2022 2 24-04-2022 2 25-04-2022 6 25-04-2022 88 25-04-2022 88 28-04-2022 54 28-04-2022 54 28-04-2022 59 28-04-2022 59
18 Replies

# Re: How to Count of invoice numbers in a column

With data in A14:B24:

=SUMPRODUCT(1/COUNTIFS(A14:A24,A14:A24,B14:B24,B14:B24))

# Re: How to Count of invoice numbers in a column

@HansVogelaar  Thank you Hans Vogelaar. This gives me count of invoices, but not date wise?

Need to fill the below table (invoice count date wise)

 Date Count of inv 24-04-2022 ?? 25-04-2022 ?? 28-04-2022 ??

Data for filling the above table -

 Date Inv Number 24-04-2022 1 24-04-2022 2 24-04-2022 2 24-04-2022 2 25-04-2022 6 25-04-2022 88 25-04-2022 88 28-04-2022 54 28-04-2022 54 28-04-2022 59 28-04-2022 59

# Re: How to Count of invoice numbers in a column

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)
)``````

# Re: How to Count of invoice numbers in a column

You could create a pivot table, adding the data to the Data Model:

You can then choose Distinct Count to summarize the Inv Number field:

Result:

# Re: How to Count of invoice numbers in a column

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

# Re: How to Count of invoice numbers in a column

``=SUM(N(IF(\$A\$5:\$A\$15=D7,MATCH(IF(\$A\$5:\$A\$15=D7,\$B\$5:\$B\$15),IF(\$A\$5:\$A\$15=D7,\$B\$5:\$B\$15),)=ROW(\$1:\$11))))``

An alternative could be this formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.

# Re: How to Count of invoice numbers in a column

Thank you so much

# Re: How to Count of invoice numbers in a column

This too works. Just curious about the logic behind selecting Row 1 to 11....

# Re: How to Count of invoice numbers in a column

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.

# Re: How to Count of invoice numbers in a column

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.

# Re: How to Count of invoice numbers in a column

``````=SUM(--
(
FREQUENCY(
(A\$14:A\$24=D14)*B\$14:B\$24,
(A\$14:A\$24=D14)*B\$14:B\$24
) > 0
)
)-1``````

# Re: How to Count of invoice numbers in a column

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))
))
)``````

# Re: How to Count of invoice numbers in a column

To illustrate @HansVogelaar solution I added it to the same file

# Re: How to Count of invoice numbers in a column

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.

# Re: How to Count of invoice numbers in a column

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.

# Re: How to Count of invoice numbers in a column

@Shivashimladka Can you help with counting these please?  The data is in L3:L17

XCT22-2494-01
XCT22-2494-02
XCT22-2494-03
XCT22-2494-03
XCT22-2494-03
XCT22-2494-03
XCT22-2494-04
XCT22-2494-04
XCT22-2494-04
XCT22-2494-04
XCT22-2494-04
XCT22-2494-05
XCT22-2494-05
XCT22-2494-06
XCT22-2494-06

# Re: How to Count of invoice numbers in a column

Do you want to count unique values?

If you have Excel 2021 or 365:

=COUNTA(UNIQUE(L3:L17))

If you have an older version, as an array formula confirmed with Ctrl+Shift+Enter:

=SUMPRODUCT(1/COUNTIF(L3:L17,L3:L17))

# Re: How to Count of invoice numbers in a column

@HansVogelaar Awesome.  Thank you