I have been using the function COUNTA, e.g., =COUNTA(G4:G9). Works! Want to replace G4 and G9 with addresses based on data, e.g. =COUNTA(ADDRESS(D1,7,4):ADDRESS(E1,7,4) where D1 and E1 contain the numbers 4 and 9. But this doesn’t work and I don’t know why. ADDRESS(D1,7,4) by itself gives me G4, and the other gives me G9. So why doesn’t it work?
Always tell what actually happens. That something doesn’t work as you expected is supposed anyway.
That’s because the colon isn’t an operator in the position where you placed it, and an address isn’t a reference like COUNTA() would need as its argument. To get a reference for an address calculated as a string you need to use the INDIRECT() function.
=COUNTA(INDIRECT(ADDRESS(D1;7;4)&":"&ADDRESS(E1;7;4)))
does it, but isn’t a really satisfactorty solution. You may prefer
=COUNTA(OFFSET($G$1;D1-1;0;E1-D1+1;1)) .
There are help pages for all the used functions.
Hallo
=COUNTA(INDEX(G1:G1000;D1):INDEX(G1:G1000;E1))
=COUNTA(INDIRECT("G"&D1&":G"&E1))
Thank you for replying. My problem is solved. No need for further comments. All the best. M