Forum Discussion
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 |
=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.
- This too works. Just curious about the logic behind selecting Row 1 to 11....
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.
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.
