Explanation
In this example, we havedatesin B5:B16 andsalesin C5:C16.
Both ranges arenamed ranges.
We then use a formula in F8 tosumthe dynamic range, and a formula in F9 tocountthe dynamic range.
In the current version of Excel, this problem can be easily solved with the XLOOKUP function.
In older versions of Excel without XLOOKUP, it’s possible for you to use INDEX and MATCH.
Both approaches are described below.
The range operator (:) is used to construct ranges.
Nevertheless, the reference is there and can be used in other ways.
The SUM then returns a new result.
Tocountresults instead of summing results, just replace the SUM function with the COUNT function.
Notice the range operator (:) sits between two separate INDEX and MATCH formulas.
The SUM function then returns a new result.
XLOOKUP supports approximate and exact matching, wildcards (* ?)
you might use INDEX to retrieve individual values, or entire rows and columns.
MATCH supports approximate and exact matching, andwildcards(* ?)
Numbers include negative numbers, percentages, dates, times, fractions, and formulas that return numbers.
