Index Match And Collect With Multiple Criteria Without Collecting Blank Cells

Jamika J.

Hello I am trying to pull in data from 2 different criteria without it collecting any blank cells, only cells with values.

=COUNTIF(IFERROR(INDEX(COLLECT({Range 1 on Second Sheet}, {1st Criteria on Second Sheet}, [1st Criteria on Primary Sheet]@row, {2nd Criteria on Second Sheet}, [2nd Criteria on Primary Sheet]@row, <>""), 1), ""))

I am not sure where I am going wrong. It worked fine when pulling in the first rows of data before I attempted to add in the countif section. It does not bring over the values from additional rows if there are blanks within in the column.

Would anyone have any suggestions?

Kelly Moore

Hello @Jamika J.

I'm happy to help. Before exploring why you are getting zero, let's clarify what you are trying to do. My guess is you are trying to count using the same criteria that is within your Collect? If yes, then the COUNTIFS function can be used directly

=COUNTIFS({1st Criteria on Second Sheet}, [1st Criteria on Primary Sheet]@row, {2nd Criteria on Second Sheet}, [2nd Criteria on Primary Sheet]@row, <>"")

Will this work for you? Please @mention me if this is not correct or if you have questions

Kelly

Jamika J.

Hello @Kelly Moore

I have the below images of what I am attempting to do. First I have to find the total points made (In blue) on my secondary sheet, then match the persons name and the Review Quarter to pull over to my primary sheet. If there are blank entries or zeros, it doesn't

Screenshot (245).png

I have to pull over the points for specific quarters, but because there are some entries where a number is not generated, it is not pulling in the correct points in the specified row. For this example, I am trying to find how to pull over the 12 points by matching the review quarter, staff member in a column without calculating the blank entries.

My formula worked for the first few entries only.

=IFERROR(INDEX(COLLECT({My Points}, {Staff Name on 1st Sheet}, [Staff Name on Second Sheet]@row, {Review Quarter on Second Sheet}, [Review Quarter on Primary Sheet]@row), 1), "")

Would I possibly need to build a new formula?

Kelly Moore

Hey @Jamika J.

Looking at the sheet designations within your formula - Do you have a total of 3 sheets you are using: a primary sheet, a Names sheet (sheet 1) and a quarter sheet (sheet 2?). If yes, this is a problem for the Index/Collect.

Also, looking at the values in blue - is the only way possible for a score of zero to obtained is by blank cells, or could someone actually receive a legit score of zero? I'm wondering if I can omit your zero scores (you could also change your column formula that produces your blue scores to show a blank instead of a zero where blanks were used to generate the number)

Kelly

Jamika J.

@Kelly Moore

Hello Kelly,

Thank you for your feedback. I am currently only using 2 sheets.

Kelly Moore

Hey @Jamika J.

Try this

=IFERROR(INDEX(COLLECT({My Points}, {My Points},AND(ISNUMBER(@cell),@cell>0),{Staff Name on 1st Sheet}, [Staff Name on Second Sheet]@row, {Review Quarter on Second Sheet}, [Review Quarter on Primary Sheet]@row), 1), "")

Will this work for you? It would be helpful to see a screenshot of your primary sheet (destination sheet) and to see a screenshot of the formula in blue column on your source sheet

Kelly