@hussein_elsayed
can you explain the formula of the equation in a simple way so that I can understand it?
Not simply or easily, but I'll try. Here, too, are some excellent resources for you to pursue for more general explanations.
LET function: https://exceljet.net/excel-functions/excel-let-function
FILTER function: https://exceljet.net/excel-functions/excel-filter-function
https://www.youtube.com/watch?v=9I9DtFOVPIg
My formulae use those two functions in particular. FILTER enables you to, well, filter a column or an entire table of data using multiple criteria. In this case, we were filtering a single column, which in the first instance was just the client/customer ID (column A); in the second instance was the size of the outstanding debt (Column F).
Having the "*" sign between sets of criteria operates as if it reads AND. So, first, the criteria
('Invoice List'!$C$4:$C$6565=Summing!C$2)*('Invoice List'!$J$4:$J$6565>=Summing!$A3)*('Invoice List'!$J$4:$J$6565<=Summing!$B3)
are filtering the desired range for either counting or summing based on
- Year (in column c) being the same as whatever is in row 2 of the summary range, which is 2019, 2020 or 2020
- and then the last two criteria determine whether the number of days is greater than or equal to the minimum date, and less than or equal to the greater date of the date ranges you specified.
The COUNTA or SUM functions took the filtered result and counted or summed the rows that came through the criteria in each case, thus resulting in one figure--a count or a sum--for each cell.
[you should experiment with the FILTER function by itself, not nested in COUNTA or SUM, to see what results. Just make sure you leave plenty of rows beneath the formula]
The LET function enables the assigning of the result of the nested COUNTA(FILTER(...)) or SUM(FILTER(....)) to a named variable--I chose "res" as short for "result", and then the closing IF function lets us look at "res" and either just display it or display 0 depending on what its value is.
I used the IF in conjunction with COUNTA because COUNTA always delivers 1 as a result even if the actual number is 0 [because it does show a single row with a text of some kind], so in the case of the COUNTA, the fist time, that closing IF reads IF(res=1,0,res) which just shows 0 if it's getting a 1, but otherwise the full count. In the case of the SUM formula, the final IF is actually an IFERROR because when there's no sum produced it gave an error message.
I know that's a lot to process, especially if, in the first place, you don't understand the various functions. That's why I recommend using the resources linked to above, first of all, and then --even more highly -- I recommend playing with the functions on simpler data arrays until what they're doing makes sense. Play with FILTER by itself, adding criteria and see the results. Then nest FILTER in COUNT or COUNTA or SUM...or any of a number of other functions, and again, see what happens. Same with LET...read the definition in that resource above--or go to YouTube and look there for instructional videos on the LET function. But try it out, use it......just reading about it in something like what I've written above will never fully teach you; we all learn more through doing, and I even emphasize "playing" in the sense of experimenting.
