Join/Collect with 3 criteria

Whalstead1

Hi everyone,

I am trying to use a Join(Collect formula to find 3 criteria. It looks like I can only have 2 criteria, but before I gave up, I wanted to see if I am missing something. Or does anyone have another way to evaluate 3 criteria? Thank you!

Paul Newcome

It looks like you have some data type inconsistencies. Some are left justified and some are right justified. This means some are numbers and some are text (leading zeros create a text string).

Insert a helper column in both sheets that have this column formula:

=[Report Time]@row + ""

Then use these helepr columns in your formula in place of directly referencing the time columns.

Paul Newcome

You can have as many range/criteria sets as you need so long as you don't exceed 4,000 characters (including spaces). Can you provide the formula you were trying that wasn't working?

Whalstead1

It was

=Join(COLLECT({Situation Unit Data Collection To Range 5}, {Situation Unit Data Collection To Range 1}, [Event Name]@row, {Situation Unit Data Collection To Range 3}, [Report Date]@row, {Situation Unit Data Collection To Range 4}, [Report Time (24 Hour Format)]@row), ",")

Everytime I tried to enter the 3rd criteria it kept asking for the deliminator for the join so I wasnt sure how to add the third.

Paul Newcome

The above should work. Can you provide a screenshot of it open in the sheet as if you are editing it?

Whalstead1

Sure - the sheet where I have the formula is here:

And this is the sheet the info comes in from.

Paul Newcome

And that's not working?

Whalstead1

No, First I was gettng #UNPARSABLE and now it is just giving me an empty cell

Paul Newcome

What does this give you?

=COUNTIFS({Situation Unit Data Collection To Range 1}, [Event Name]@row, {Situation Unit Data Collection To Range 3}, [Report Date]@row, {Situation Unit Data Collection To Range 4}, [Report Time (24 Hour Format)]@row)

Whalstead1

Im getting Unparseable with that

Whalstead1

When I do it with only 2 criteria it works, but three, it breaks

Paul Newcome

Ok. What about this one?

=COUNTIFS({Situation Unit Data Collection To Range 4}, [Report Time (24 Hour Format)]@row)

Whalstead1

I get a 0 for that

Paul Newcome

How are you populating both of those columns exactly (there cross sheet as well as the cell reference)?

Whalstead1

The main information comes into the sheet through a form. But for the 3 fields that are critieria, I type them in the second sheet or copy and paste

Paul Newcome

It looks like you have some data type inconsistencies. Some are left justified and some are right justified. This means some are numbers and some are text (leading zeros create a text string).

Insert a helper column in both sheets that have this column formula:

=[Report Time]@row + ""

Then use these helepr columns in your formula in place of directly referencing the time columns.

Whalstead1

That worked. Thank you Paul!