arraysexcelcountsumproduct

EXCEL Sumproduct COUNT 2 AND condition every nth COLUMN, counting blank and not blank


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

  1. Check and Count every 4th column range for NOT blank starting column B
  2. Check and Count Every 4th column range for IS BLANK starting column D
  3. Final Result: Count the number of cell where the Order Label Not Blank and Amount Label Blank condition is both satisfied
  4. Note there is 1 blank column gap every column label for spacing
  5. Individually the formula works but if i try to combine the formula to consolidate it, it does not work anymore
  6. simplified the sample sheet but actual data has more columns but same case and setup of every nth column

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!


Solution

  • 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.

    enter image description here