Please enable JavaScript to view the comments powered by Disqus.


SUMPRODUCT(...(...MOD(...ROW(...INDIRECT(..."A"&B1&":A"&B2)),B3)=0)*1)

Count numbers between 74 and 174 that are divisible by 5.

This formula checks for the remainder of the numbers by using MS Excel formula MOD(), if the returned value is zero then the number is perfectly divisible, otherwise not. In case it is perfectly divisible, we count 1 and that is added by SUMPRODUCT().


Used Functions

Category: Math and trigonometry

Returns the sum of the products of corresponding array components

Syntax

SUMPRODUCT(array1, [array2], [array3], ...)

Category: Math and trigonometry

Returns the remainder from division

Syntax

MOD(number, divisor)

Category: Lookup and reference

Returns the row number of a reference

Syntax

ROW([reference])

Category: Lookup and reference

Returns a reference indicated by a text value

Syntax

INDIRECT(ref_text, [a1])

Lets chat on this