looking for help with a formula that uses one "text", and one "match" or "has" condition.

moniraza

the closest I have gotten to getting some results is using: =IF(AND(CONTAINS("Q8", {Expenditure Report Collection Range1}), HAS({Expenditure Report Collection Range 2}, [Primary Column]@row)), "ok","missing")

maybe I don't understand order of operations but it's giving me a "ok" on a cell that should be missing. I need to use "Q8" text because the range has different drop-down selections with words and numbers. The other criteria is if it matches a number in the range, but I cant seem to get a "match" formula to work. HAS is providing some results.

Paul Newcome

Try a COUNTIFS inside of an IF statement instead:

=IF(COUNTIFS({Range 1}, CONTAINS("Q8", @cell), {Range 2}, HAS(@cell, [Primary Column]@row) > 0, "ok", "missing")

moniraza

thank you for your quick response! I am getting "inccorect argument". This is what i plugged in, but I think the ("Q8",@cell) is where I'm not understanding what is being asked to reference.

=IF(COUNTIFS({Expenditure Report Collection Range 1}, CONTAINS("Q8", {Expenditure Report Collection Range 4}), {Expenditure Report Collection Range 2}, HAS([Primary Column]@row) > 0, "ok", "missing"))

Paul Newcome

Try leaving the @cell references exactly as I had them. You should be able to use my formula after only adjusting the two cross sheet references.

moniraza

I'm still getting "incorrect argument". maybe a screen shot will help. The Primary Sheet (P.S.) contains the same contract No. as the reference sheet (R.S) range, and I want a "yes" or "no" if the Primary Column@row contract no. is found within the R.S. contract no. range, as well as having a "Q8" in the second range (reporting period) of the R.S.

Paul Newcome

What is your new formula exactly?

moniraza

=IF(COUNTIFS({Expenditure Report Collection Range 1}, CONTAINS("Q8", @cell), {Expenditure Report Collection Range 2}, HAS(@cell, [Primary Column]@row) > 0, "ok", "missing"))

could it be the ranges im referencing? Range 1 I'm referencing the reporting period, and range 2 im referencing the contract no.

Paul Newcome

There is a misplaced parenthesis. Try this one:

=IF(COUNTIFS({Expenditure Report Collection Range 1}, CONTAINS("Q8", @cell), {Expenditure Report Collection Range 2}, HAS(@cell, [Primary Column]@row)) > 0, "ok", "missing")