IF AND CONTAINS with >=TODAY(-7)

A. Rose

Hi,

I have a formula I need help with please,

=IF(AND(CONTAINS([Client Name]@row, {Sheet A Range 1}), IF({Sheet A Range 2}, >=TODAY(-7))), "Match")

______________

Name = Text column

Sheet A Range 1 = Text column

Sheet A Range 2 = Date column

______________

Any idea how to correct this #INVALID DATA TYPE?

Thank you!

Find more posts tagged with

Accepted answers

A. Rose

"Paul Newcome

Paul Newcome ✭✭✭✭✭✭

4:59PM

Give this a try:

=IF(COUNTIFS({Sheet A Range 1}, CONTAINS([Client Name]@row, @cell), {Sheet A Range 2}, @cell>= TODAY(-7))> 0, "Match")

10xViz.com

Come see me at ENGAGE 2023! If you can't find me roaming the floor, I will most likely be at the 10xViz Partner Booth. I will also be doing a short demonstration in the Experience Hub Theater on Tuesday! "

Genevieve P.

Hey @A Rose

In your second COUNTIFS you'll just need to remove the >1 bit of it.

=IF(COUNTIFS([Client Name]:[Client Name], [Client Name]@row) > 1, COUNTIFS([Client Name]:[Client Name], [Client Name]@row))

What would you like it to do if the count is just 1? Currently this will return a blank cell.

Cheers,

Genevieve

All comments

Paul Newcome

What exactly are you trying to accomplish?

A. Rose

I want to see if this specific client was added to sheet A within the past 7 days.

Sheet A Range 2 = Date Created

thanks

Paul Newcome

Give this a try:

=IF(AND(CONTAINS([Client Name]@row, {Sheet A Range 1}), IF({Sheet A Range 2}, >=TODAY(-7))), "Match")

=IF(COUNTIFS({Sheet A Range 1}, CONTAINS([Client Name]@row, @cell), {Sheet A Range 2}, @cell>= TODAY(-7))> 0, "Match")

A. Rose

Hi,

New formula:

=IF(COUNTIFS({Sheet A Range 1}, CONTAINS([Client Name]@row, @cell), {Sheet A Range 2}, @cell >= TODAY(-7))> 0, "Match")

The response is:

#INCORRECT ARGUMENT SET

Thanks,

Paul Newcome

Double check that both ranges are in fact set as single column references.

A. Rose

Genius!

Reference 1 was originally a vlookup referencing two columns...

Thank you so much @Paul Newcome !!

A. Rose

"Paul Newcome

Paul Newcome ✭✭✭✭✭✭

4:59PM

Give this a try:

=IF(COUNTIFS({Sheet A Range 1}, CONTAINS([Client Name]@row, @cell), {Sheet A Range 2}, @cell>= TODAY(-7))> 0, "Match")

10xViz.com

Paul Newcome

Happy to help. 👍️

A. Rose

Hi @Paul Newcome,

I have a similar question,

I hope you can help out on this as well,

I want to see - for clients that are on this sheet more than once - how many times this client is on this sheet,

My formula returns 0 for those with more than one name in the name column,

=IF(COUNTIFS([Client Name]:[Client Name], [Client Name]@row) > 1, COUNTIFS([Client Name]:[Client Name], [Client Name]@row > 1))

Thank you,

Genevieve P.

Hey @A Rose

In your second COUNTIFS you'll just need to remove the >1 bit of it.

=IF(COUNTIFS([Client Name]:[Client Name], [Client Name]@row) > 1, COUNTIFS([Client Name]:[Client Name], [Client Name]@row))

What would you like it to do if the count is just 1? Currently this will return a blank cell.

Cheers,

Genevieve

Paul Newcome

@A Rose In addition to @Genevieve P.'s response, you will also need to incorporate a HAS or CONTAINS function depending on the source data. If the source data is in a multi-select column, your criteria with the HAS function would be

HAS(@cell, [Client Name]@row)

If it is not a multi-select column, you would use the CONTAINS function.

CONTAINS([Client Name]@row, @cell)

A. Rose

Hi @Genevieve P.,

Thanks for that!

@Paul Newcome,

Those are regular columns, thanks for the tip! you're always here to help out and I appreciate that!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.