DO NOT Count Cells With Formula Without Data In Them For Dynamic Chart Range

[PPriest]

I am trying to create a chart with a dynamic range pointing to cells that have formulas in them (in this example - the yellow range). If the formula returns no data, I want those cells to be ignored.

This video (How to Create a Dynamic Chart Range in Excel) shows what I am trying to do, and it would work if I could get the OFFSET COUNTIF formula to work.

Any suggestions or different approaches? I should mention that the data the formula will be returning in Column D could be either numeric or alphabetic or a combination of both.

 

[GraH]

Maybe in your case by replacing the COUNTIF(E3:E23,"<>*") with COUNTIF(E3:E23,">0")

 

[PPriest]

Maybe in your case by replacing the COUNTIF(E3:E23,"<>*") with COUNTIF(E3:E23,">0")

Close but no cigar. It drops off the last 2 numbers in that column (in red).

 

[GraH]

Because you have not only numbers in your range... Not the same picture as before.

Maybe this works =OFFSET(E3,,,COUNTA(E3:E23)-COUNTIF(E3:E23,""))

 

[GraH]

or =OFFSET(E3,,,MATCH(TRUE,E3:E23="",0)-1)

 

[PPriest]

or =OFFSET(E3,,,MATCH(TRUE,E3:E23="",0)-1)

That seems to have done the trick for that piece. Thank you, GraH! I appreciate the help. Now onto the next piece.

 

[GraH]

Cool, glad it finally worked. Thx for the feedback.