Countif and Contain Cross Sheet reference formula issues
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.
Best Answer
Answers
-
Its till returns a 0 even though I know the range I selected in the sheet has at least 2 of the matching criteria.
-
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.
-
@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.
-
@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.
-
Here you go.
Thanks so much for looking into this.
-
Hi Ryan, thanks for looking into this, however, now it counts all the rows with a matching answer.
Thank you so much!
-
@John Kippers What were you wanting it to count?
-
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
-
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
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!