Return Countif Values

[manthony]

Hi, I am trying to figure out a formula starting in cell B9 on Sheet1 that will count the number of rows greater than 0 in column V on a different sheet (Sheet2) and then return the first value it counted.

Then in cell B10 a formula that would return the second value counted in column V on Sheet2. Cell B11 would return the third value in column V on Sheet2 and so on until it has returned all values in column V on Sheet2.

In column V of Sheet2 the values are randomly disbursed (for example the first value is in cell V20, the second value is in cell V27, and blank between V20-V27) and not one right after the other. It is unknown how many value will appear and in what rows of column V on Sheet2. Does anyone have any ideas? Thank you in advance for the help.

 

[Aladin Akyurek]

In B10 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX(Sheet2!$V$2:$V$100,SMALL(IF(ISNUMBER(Sheet2!$V$2:$V$100),ROW(Sheet2!$V$2:$V$100)-ROW(Sheet2!$V$2)+1),ROWS($B$10:B10))),"")

 

[manthony]

Hi Aladin,

Your formula works great. Thank you so much.

One last thing, is it possible to modify the formula to get the corresponding value (this time a date) from column A of Sheet2 and have it appear in cell A9? The catch is that in column A every row has a date in it.

 

[Aladin Akyurek]

Hi Aladin,

Your formula works great. Thank you so much.

One last thing, is it possible to modify the formula to get the corresponding value (this time a date) from column A of Sheet2 and have it appear in cell A9? The catch is that in column A every row has a date in it.

In A9 just enter:

=COUNT(Sheet2!$V$2:$V$100)

This counts the relevant records.

In A10 control+shift+enter, not just enter, and copy down:

=IF(ROWS($A$10:A10)>$A$9,"",INDEX(Sheet2!$A$2:$A$100,SMALL(IF(ISNUMBER(Sheet2!$V$2:$V$100),ROW(Sheet2!$A$2:$A$100)-ROW(Sheet2!$A$2)+1),ROWS($A$10:A10))))

In B10 control+shift+enter, not just enter, and copy down:

=IF($A10="","",INDEX(Sheet2!$V$2:$V$100,SMALL(IF(ISNUMBER(Sheet2!$V$2:$V$100),ROW(Sheet2!$V$2:$V$100)-ROW(Sheet2!$V$2)+1),ROWS($B$10:B10))))

 

[manthony]

Hi Aladin,

I cannot thank you enough. Both formula work great. Thank you so much !!!

 

[Aladin Akyurek]

Hi Aladin,

I cannot thank you enough. Both formula work great. Thank you so much !!!

You are welcome.