Formula Question See Below

Brandon03

IF PDCA range 4 finds ( anywhere ) "Monkeys" , and you can find "OK 3" in Range 1 return a BLANK but if it cant return 1

=COUNTIFS({PDCA Range 4}, HAS(@cell, "Monkeys"), {PDCA Range 1}, FIND("OK 3", @cell) = blank)

Lidiya S.

Understood, the best way to do this is to create a report that would filter for PIC names that have values in the OPEN ITEMS column.

Create > Report

Name it OPEN ITEMS

Source Sheet: The sheet you are using above

Columns to Display: PIC, OPEN ITEMS

Filter Criteria:

Select Field = "OPEN ITEMS"

Select Type = "is greater than"

Enter Value = 0

Then go to the dashboard and make this report the source for your graph.

@ me to let me know how it works!

Lidiya Shutaya

Lidiya S.

Brandon03,

Please clarify, what exactly is the goal of the formula: To output a 0 if the cell contains "Monkeys" AND "OK 3"?

Is this a row formula or sheet formula?

If it is a row formula I would suggest the formula be adjusted to:

=IF(AND(FIND("Monkeys", [Task Name]@row) > 0, FIND("OK 3", [Task Name]@row) > 0), "1", "0")

The issue you might be having is that the =FIND formula actually returns the location of the string if it is found. For example is the cell stated -

Monkeys OK 3

FIND("Monkeys", [Task Name]@row would return 1

BUT

FIND("OK 3", [Task Name]@row would return a 9.

If this is for the sheet, I would recommend creating a column and putting the formula above in it, then counting how many rows return a "1".

Please reply with any questions!

Lidiya Shutaya

lidiay@ddbconsultants.ca

Brandon03

I don't want the 0 to show up in my graph and just want it to be blank.

Lidiya S.

No problem! Instead of "0" use "".

=IF(AND(FIND("Monkeys", [Task Name]@row) > 0, FIND("OK 3", [Task Name]@row) > 0), "1", "")

Do you want all the names to show up in the graph or only the ones with values?

Lidiya Shutaya

lidiay@ddbconsultants.ca

Brandon03

Only the ones with values

Lidiya S.

Understood, the best way to do this is to create a report that would filter for PIC names that have values in the OPEN ITEMS column.

Create > Report

Name it OPEN ITEMS

Source Sheet: The sheet you are using above

Columns to Display: PIC, OPEN ITEMS

Filter Criteria:

Select Field = "OPEN ITEMS"

Select Type = "is greater than"

Enter Value = 0

Then go to the dashboard and make this report the source for your graph.

@ me to let me know how it works!

Lidiya Shutaya