Countif and Contain Cross Sheet reference formula issues

John Kippers

Hi, looking for some help with a formula...mine keeps coming back as 0

I am trying to count how many times a Model# shows up in another sheets column that matches a cell in the Model row of my first sheet by using the Countif Contains formulas.

Here is what I am using.

=COUNTIFS({Selling Used/Consignment Sheet Range 2}, Model@row, {Selling Used/Consignment Sheet Range 2}, CONTAINS(Model@row, @cell))

Thanks in advance.

Ryan Sides

@John Kippers

Thanks for those screenshots. This one should do it...

=COUNTIFS({Selling Used/Consignment Sheet Range 2}, FIND(Model@row, @cell) > 0)

Please let me know if that one works out.

Ryan

Ryan Sides

@John Kippers

Hi John, try this one...

=COUNTIFS({Selling Used/Consignment Sheet Range 2}, CONTAINS(Model@row, @cell))

Please let me know if that works for you!

Ryan

John Kippers

Its till returns a 0 even though I know the range I selected in the sheet has at least 2 of the matching criteria.

John Kippers

I have also tried to use a true, false to populate a cell and it keeps returning false.

=IF(CONTAINS(Model@row, {Wanted Used/Consignment Sheet Range 1}), "True", "False")

All I really want to do is have a cell populated with a check box if there is a match between two rows in two separate sheets, if there is I can create an automation that sends a contact a notification that there is a match between a seller and a buyer.

John Kippers

@Ryan Sides A quick additional note, the sheets that I am working with are sheets that are populated by a row copy from a master sheet that is populated by a form entry.

Not sure if this will have any effect on the issue, but thought it may be helpful to add the detail.

Ryan Sides

@John Kippers Can you post screenshots of both sheets with actual data? Also, please double check that your {Selling Used/Consignment Sheet Range 2} is pointing to the FULL COLUMN of the other sheet, not just a single cell there.

John Kippers

Here you go.

Thanks so much for looking into this.

Ryan Sides

@John Kippers

Thanks for those screenshots. This one should do it...

=COUNTIFS({Selling Used/Consignment Sheet Range 2}, FIND(Model@row, @cell) > 0)

Please let me know if that one works out.

Ryan

John Kippers

@Ryan Sides

Hi Ryan, thanks for looking into this, however, now it counts all the rows with a matching answer.

Thank you so much!

Ryan Sides

@John Kippers What were you wanting it to count?

John Kippers

@Ryan Sides

That was not my original intention, but it is an added benefit that will help add some data to a dashboard on current number of Model matches.

It actually turned out to be very helpful.

My original intent was to just check a box to be able to send out a notification of a match, but this works out well.

I can still send out notifications based on whether or not there is a number greater than 0, so it all works out.

Thanks again so much for your help.

John

Ryan Sides

@John Kippers

If you want it to check a box, you can use this one in a checkbox field:

=if(COUNTIFS({Selling Used/Consignment Sheet Range 2}, FIND(Model@row, @cell) > 0) 1, 0)

Glad to hear it worked! And about the extra benefits. haha

-Ryan