I use this a lot, but not enough to remember how to do it each time!
Say you want to reference a range of cells but only the ones with values. First up you need to count the cells that have values. you do this with, for example:
Our data is in A1:A10
for numbers and
for words / strings
Let us say we put this formula in B1, and assume we have 6 entries, so the result will be 6. We should also assume that the data values are contigous, that is one after the other. If they aren’t you will have to first sort the range.
We now want to reference the range with only the values in it – A1:A6, and we use the indirect() function to do this. let us assume they are numbers and we want to add them up.
The normal formula would be:
Using indirect() to bring in the value from B1: