add a filter to a formula, calculate ALL columns with any value

OshaK

I have a project that I had to create a metrics sheet.

this sheet gets data from another sheet (which is populated by a form).

I have two questions:

1. How do I flag a column ONLY if ALL columns are filled in the metrics (I might not be able to make them required fields in the form of the source sheet for a number of reasons, and they might be populated manually after the form is submitted)
2. How to add to the metrics sheet ONLY the rows that have 'Custom" and "Chief Residency Session" and exclude 'Qualtity improvement' (alternatively, how to ONLY copy data for "Custom" topic and exclude others? I only have this Presentation Topic column in the source sheet, not the metrics sheet, but can add it, if needed.

The source sheet:

The metrics sheet (all data is linked to the source sheet) with this formula:

example: =IFERROR(INDEX(DISTINCT({Speaker name}), Number@row), "")

thank you.

Jeff Reisman

@OshaK

Question 1: Add a checkbox column to your source sheet. Use a formula to count blank cells on the row, then insert that into an IF, as follows:

=IF(COUNTIFS([First column name]:[Last Column name], ISBLANK(@cell)) = 0, 1, 0)

Logic: If the count of blank cells on this row is 0, check this box, otherwise, don't check the box.

Question 2: Hard to answer this since I have no idea what metrics you're trying to capture from the source sheet or how that relates to any columns listed on the metrics sheet. This doesn't really look like metrics data (counting/summarizing values from another sheet) but instead looks like you're linking and parsing data from another sheet.

Jeff Reisman

@OshaK

When I build a metrics sheet, I'm generally calculating totals and collecting data that is tied to certain values in the source sheet. For instance, if I want to count the number of invoices assigned to an employee, I would list all the employees on the metrics sheet, and run a COUNTIFS against the source sheet where {Source Sheet Employee Name} equals Employee@row. If my source sheet has many rows each for Joe, Sally, Bob, and Jill - but I only want my metrics sheet to tell me about Joe, Sally, and Bob - I would just leave Jill off the list of employees on the metrics sheet (sorry Jill!)

Jeff Reisman

@OshaK

Question 1: Add a checkbox column to your source sheet. Use a formula to count blank cells on the row, then insert that into an IF, as follows:

=IF(COUNTIFS([First column name]:[Last Column name], ISBLANK(@cell)) = 0, 1, 0)

Logic: If the count of blank cells on this row is 0, check this box, otherwise, don't check the box.

Question 2: Hard to answer this since I have no idea what metrics you're trying to capture from the source sheet or how that relates to any columns listed on the metrics sheet. This doesn't really look like metrics data (counting/summarizing values from another sheet) but instead looks like you're linking and parsing data from another sheet.

OshaK

@Jeff Reisman thank you so much. You're right on Q2. I named it incorrectly but I'm parsing data from another sheet. Is it possible to exclude some?

For example in the source sheet I have another column (which is not in metrics sheet, but i can add it)

that has a drop down menu:

• Item 1
• Item 2
• Item 3

Can i only display all the data in teh metrics sheet for the rest of the column but just for 2 items and exclude one, it wouldn't appear in the metrics sheet at all?

thank you

Jeff Reisman

@OshaK

When I build a metrics sheet, I'm generally calculating totals and collecting data that is tied to certain values in the source sheet. For instance, if I want to count the number of invoices assigned to an employee, I would list all the employees on the metrics sheet, and run a COUNTIFS against the source sheet where {Source Sheet Employee Name} equals Employee@row. If my source sheet has many rows each for Joe, Sally, Bob, and Jill - but I only want my metrics sheet to tell me about Joe, Sally, and Bob - I would just leave Jill off the list of employees on the metrics sheet (sorry Jill!)

OshaK

Thank you so much @Jeff Reisman!