 # Help with SUMPRODUCT formula - Include 0 and 1 but not 10

Hello! I have a column that contains numbers 0 through 10. I'd like to count (with filter) cells that are <=6. I have this formula, but it is including cells with 10. How do I exclude 10?

=SUMPRODUCT(SUBTOTAL(3,OFFSET(G2:G286,ROW(G2:G286)-MIN(ROW(G2:G286)),,1)),ISNUMBER(SEARCH("6",G2:G286))+ISNUMBER(SEARCH("5",G2:G286))+ISNUMBER(SEARCH("4",G2:G286))+ISNUMBER(SEARCH("3",G2:G286))+ISNUMBER(SEARCH("2",G2:G286))+ISNUMBER(SEARCH("1",G2:G286))+ISNUMBER(SEARCH("0",G2:G286))+0)

6 Replies

# Re: Help with SUMPRODUCT formula - Include 0 and 1 but not 10

``=SUMPRODUCT(SUBTOTAL(3,OFFSET(G2:G286,ROW(G2:G286)-MIN(ROW(G2:G286)),,1))*((G2:G286=0)+(G2:G286=1)+(G2:G286=2)+(G2:G286=3)+(G2:G286=4)+(G2:G286=5)+(G2:G286=6)))``

This returns the expected result if i correctly understand what you want to do. # Re: Help with SUMPRODUCT formula - Include 0 and 1 but not 10

Even somewhat shorter than from @Quadruple_Pawn :

``=SUMPRODUCT(SUBTOTAL(3,OFFSET(\$G\$2,ROW(\$G\$2:\$G\$286)-ROW(\$G\$2),0)),--(\$G\$2:\$G\$286<=6))``

# Re: Help with SUMPRODUCT formula - Include 0 and 1 but not 10

@Quadruple_Pawn This works perfectly, thank you!

# Re: Help with SUMPRODUCT formula - Include 0 and 1 but not 10

@dscheikey This is awesome, shorter is great, thank you!

# Re: Help with SUMPRODUCT formula - Include 0 and 1 but not 10

A 365 solution.

Swap out 'numbers' with your range.

``=REDUCE(0,numbers,LAMBDA(a,v,IF(AND(SUBTOTAL(2,v)=1,v<=6),a+1,a)))``

# Re: Help with SUMPRODUCT formula - Include 0 and 1 but not 10

@Patrick2788 This looks very interesting, I will need to review this as I am not familiar. Thank you!