I have averaged values across a number of non-contiguous discrete cells in a row using the average function, which conveniently ignores blank cells. These cells are computed by array formulas and so many of them contain "" (generated by an iferror). There are many intervening cells containing other kinds of unrelated data so I'm unable to use column ranges. The average calculation is done via:
=IFERROR(ROUND(AVERAGE(H3,J3,N3,O3,S3,T3,V3,W3,Y3,AC3,AD3,AE3,AG3,AI3,AM3,AN3,AO3,AP3,AQ3,AS3,AW3,AY3,BA3,BB3,BG3,BH3,BJ3,BK3,BL3,BN3,BO3,BU3,BV3,BX3,CD3,CE3,CG3,CI3,CM3,CN3,CO3,CP3,CR3,CS3,CT3,CV3,CZ3,DA3,DB3,DD3,DE3,DG3,DH3),0),"")
which works just fine. I also want to count how many non-blank cells were present. However the COUNTA function does not ignore the "" values, so this returns the total number of cells present:
=COUNTA(H4,J4,N4,O4,S4,T4,V4,W4,Y4,AC4,AD4,AE4,AG4,AI4,AM4,AN4,AO4,AP4,AQ4,AS4,AW4,AY4,BA4,BB4,BG4,BH4,BJ4,BK4,BL4,BN4,BO4,BU4,BV4,BX4,CD4,CE4,CG4,CI4,CM4,CN4,CO4,CP4,CR4,CS4,CT4,CV4,CZ4,DA4,DB4,DD4,DE4,DG4,DH4)
The various solutions for overcoming this problem, like using COUNTIF and SUMPRODUCT only work on cell ranges. How can I easily do this without having a count formula for each cell individually?
Any formula used will be auto-propagated down many many rows in a sheet.
