Multi Formula/Sheet Help

Been struggling with this for ages, I can get the two elements of this formula to work separately but not together - I hope the below makes sense!

I am trying to get a formula to:

A) Search a column on another sheet to see if a reference number is present

The below works but gives me the total count

=COUNTIF({SHEET1}, REFERENCE1)

Return a result based on if a tick box is unchecked for the above confirmed reference

There would only be one unchecked entry on the other sheet of the reference number and loads with it checked, I only need to confirm if the reference is present and the tickbox is unchecked on that line.

Hope you can help, thanks

Find more posts tagged with

Sheets

Formulas

Support

Comments

Paul Newcome

How would you want your result displayed?

WSC

Just a boolean response for if the box is ticked or not good, generally the reference would always be present.

Thanks for help Paul

Paul Newcome

You're on the right track. Give this a try...

=IF(COUNTIFS({Reference Sheet Rang 1}, [Column Name]@row, {Reference Sheet Range 2}, 0) = 1, 1)

{Reference Sheet Range 1}: The column on your reference sheet containing the list of numbers.

[Column Name]@row: Simply change "Column Name" to the appropriate column name containing the number on your target sheet (where you're putting the formula).

{Reference Sheet Range 2}: The column on your reference sheet containing the checkboxes.

What this does is it will look on your reference sheet for the value that is in the same row as your formula. It will then count how many time it finds an unchecked box next to that number on your reference sheet. If that count is 1, then it will check the box where you have your formula. Otherwise it will remain unchecked letting you know that either no instances or more than one instance was found (which I assume are both issues you want to address by the sound of your post).

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…