Good Day to all! as my title suggest I am having a problem with the sumproduct formula to get the result that for the following condition
working count Not blank formula every 4th column from starting column
=SUMPRODUCT(--((MOD(COLUMN($B$2:$H$3)-COLUMN(B2),4)=0)*NOT(ISBLANK($B$2:$H$3))))
working count blank formula every 4th column from starting column
=SUMPRODUCT(--((MOD(COLUMN($D$2:$H$3)-COLUMN(D2),4)=0)*(ISBLANK($D$2:$H$3))))
my code when i Combine the 2 codes but not working N/A Error or sometime VALUE error if I play around with the ( ) bracketing
=SUMPRODUCT(
--((MOD(COLUMN($B$2:$H$3)-COLUMN(B2),4)=0)*NOT(ISBLANK($B$2:$H$3)))
*--((MOD(COLUMN($D$2:$H$3)-COLUMN(D2),4)=0)*(ISBLANK($D$2:$H$3)))
)
please see sample screenshot for reference.
sample expected data and result
Thank You!
This is the formula for the two partial results and the final result
Cell E7: =SUM(--NOT(ISBLANK(CHOOSECOLS(B2:H3,SEQUENCE(1,(COLUMNS(B2:H3)+1)/4,1,4)))))
Cell E8: =SUM(--ISBLANK(CHOOSECOLS(B2:H3,SEQUENCE(1,(COLUMNS(B2:H3)+1)/4,3,4))))
Cell E9: =SUM(--NOT(ISBLANK(CHOOSECOLS(B2:H3,SEQUENCE(1,(COLUMNS(B2:H3)+1)/4,1,4))))*ISBLANK(CHOOSECOLS(B2:H3,SEQUENCE(1,(COLUMNS(B2:H3)+1)/4,3,4))))
The partial formulas choose only the necessary columns of the range and do the sum of the specific cells, while the final result multiply the logical results and then sum them.