Forum Discussion
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 |
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-06Do 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))
HansVogelaar Awesome. Thank you
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)) )) )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.
=SUM(-- ( FREQUENCY( (A$14:A$24=D14)*B$14:B$24, (A$14:A$24=D14)*B$14:B$24 ) > 0 ) )-1A 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) )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) )
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 =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.
