Search code examples
google-sheetssumproduct

SUMPRODUCT to count multiple cells in multiple columns with multiple criteria

I need some help here, I have a table where I need to count the number of cells containing the word "Late" and all blank cells.

I am using this formula: =Sumproduct("$C$17:$AF$30"={"","Late"})

Here the sample of my table

Solution

  • Similar to the link in the comment, but plus a twist (to count the blank).

    total = a + b

    where a = number of "Late" with the defined criteria b = number of blank

    a is =countif(offset($C$3,match($V29,$B$3:$B$13,0)-1,0,1,(today()-HC!$M$17)+1),"Late")

    b is =COUNTBLANK(offset($C$3,match($V29,$B$3:$B$13,0)-1,0,1,(today()-HC!$M$17)+1))

    Idea: use match() to get the criteria today()-ref to get the width, to adjust the count/countblank range.