Plot conditional chart in Google Sheets and automate the plotting

Question

I have data in Google Sheets as shown in attached first photo. I would like to plot Date v/s Vol in a chart. However, I would also like to indicate red 'X' (or 'O' or anything) to the datapoints where Condition1, Condition2, Condition3 are all = Yes (see second photo). These are the rows indicated in yellow color.

1. How to accomplish such chart plotting (second photo) in Google Sheet chart?

2. Is there any way to build a formula which takes data location (in this case B2:F38) as input and automatically plots such chart starting at, say, cell H6?

Link to file: https://docs.google.com/spreadsheets/d/102uEOgYxt4JfJQuYfroBZnaX3CCbmavD3eSh--qDJh4/edit?usp=sharing

Answer 1

In your situation, how about using the following sample formula?

Sample formula:

When your sample Spreadsheet is used, please put this formula to the cell "G3".

=ARRAYFORMULA(IF((D3:D="Yes")*(E3:E="Yes")*(F3:F="Yes")=1,C3:C,""))

• In this formula, the columns "D", "E", and "F" are yes, and the value of column "C" is returned.

Testing:

When this formula is used, the values are retrieved as follows. And, create a chart using the columns "B", "C" and "G". And, when the circle of the values of column "G" is large, the following result is obtained.

Note:

• When you use X mark as the plot of column "G", you can obtain the chart with X.