Ronderbecke
Board Regular
- Joined
- Oct 4, 2017
- Messages
- 73
Currently I am having trouble getting a count of the columns needed. I am not sure why the formula I am working on is not doing the trick and figure it might have something to do with how this data is entered? Currently, its pulling data from individual sheets. The data is either a date, a date that has a link inserted to it, "none", or its blank. I want to only calculate the cells that have the date or the date with a link. The formula structured was this:
=SUMPRODUCT(IF(MOD(Column(H12:FB)-Column(H12),7)=0,IF(ISTEXT(""),H12:FB<>0)))
but its not working. It works if I do it the long way, but with constantly adding more, it would be way easier if it calculated? Here is what I have to do to get correct stats:
=COUNTA(H12:H80,R12:R80,AB12:AB80,AL12:AL80,AV12:AV80,DD12:DD80,CT12:CT80,DN12:DN80,DX12:DX80,ER12:ER80,FB12:FB80)-counta(IF(H12:H80,R12:R80,AB12:AB80,AL12:AL80,AV12:AV80,DD12:DD80,CT12:CT80,DN12:DN80,DX12:DX80,ER12:ER80,FB12:FB80),"NONE")
Id like to just start on H12 going down the full column and count every 7 columns.
=SUMPRODUCT(IF(MOD(Column(H12:FB)-Column(H12),7)=0,IF(ISTEXT(""),H12:FB<>0)))
but its not working. It works if I do it the long way, but with constantly adding more, it would be way easier if it calculated? Here is what I have to do to get correct stats:
=COUNTA(H12:H80,R12:R80,AB12:AB80,AL12:AL80,AV12:AV80,DD12:DD80,CT12:CT80,DN12:DN80,DX12:DX80,ER12:ER80,FB12:FB80)-counta(IF(H12:H80,R12:R80,AB12:AB80,AL12:AL80,AV12:AV80,DD12:DD80,CT12:CT80,DN12:DN80,DX12:DX80,ER12:ER80,FB12:FB80),"NONE")
Id like to just start on H12 going down the full column and count every 7 columns.