Countif and row
- Thread starter ysherriff
- Start date
I figured it out. It multiplies the row you are currently on by 59 and then count in the raw table are the values that are less than or equal to 59 * the row
the second row does the same thing. It multiplies the row, for instance the 2nd row time 59 which equals 119 and then gets count of value that are less then 119 in the raw table and then subtract that total from the preceding rows.
Thanks for your help
the second row does the same thing. It multiplies the row, for instance the 2nd row time 59 which equals 119 and then gets count of value that are less then 119 in the raw table and then subtract that total from the preceding rows.
Thanks for your help
Somendra Misra
Excel Ninja
Hi ysherriff,
ROWS function count no. of rows in the given range.
so your first formula count any value in the range L2:L3238 whose value is less than equal to 59 as Rows($B$5:B5) = 1 * 59 will give 59. (B5:B5 has only 1 row no 5 in the Rows function)
Second formula, will count any value in the same range whose values is less than or equal to 2*59. and than substract sum C5:C5.
Here Rows will give 2 as the range passed to ROWS function has two rows B5:B6, so 5,6 two row.
Note the rows function is using dynamic expanding range, so if you copy the formula down the range will expand automatically to 1,2,3,.... and so on.
Regards,
ROWS function count no. of rows in the given range.
so your first formula count any value in the range L2:L3238 whose value is less than equal to 59 as Rows($B$5:B5) = 1 * 59 will give 59. (B5:B5 has only 1 row no 5 in the Rows function)
Second formula, will count any value in the same range whose values is less than or equal to 2*59. and than substract sum C5:C5.
Here Rows will give 2 as the range passed to ROWS function has two rows B5:B6, so 5,6 two row.
Note the rows function is using dynamic expanding range, so if you copy the formula down the range will expand automatically to 1,2,3,.... and so on.
Regards,
Mike H..
Active Member
Hi,
ROWS($B$5:B5)*59 evaluates as 1*59 and returns 59. It does that because there is 1 row (row5) in the ROWS range. So as posted the formula counts the number of cells in that range L2:L3238 that are <=59.
Drag the formula down 1 row and because the first $B$5 is absolute we get ROWS($B$5:B6) or 2 rows so we now get 2*59=118 so the formula now counts the number of cells <=118.
Whoever write this could have used ROWS($B$1:B1)*59 and got exactly the same result and IMHO it'd much more intuitive.