IF AND In Between Formula

Samantha S.

I'm attempting to mark whether a sale is valid based on the following criteria:

1. Clinic must have had an AHHI-1 or AHNN-1 tracking code and
2. Invoiced Date on the sale must be between the Start and End Date of the event.

I've tried this multiple different ways but keep getting errors. Here is what I have:

=IF(AND([Clinic ID]@row = {Clinic ID}, [Invoiced Date]@row >= {Start Date}, [Invoiced Date]@row <= {End Date}, OR({Code} = "AHHI-1", {Code} = "AHNN-1"), 1, 0))

Validation Sheet

Event Tracking Sheet

Paul Newcome

Ok. 2 things… I see the issue here, and I have a suggestion / tip for other headaches.

The issue here… When you manually type in a number, it is stored as numerical data. When you use a LEFT function, it is stored as text data. While they may look the same (aside from left vs right justified in the cell which is what led me here), they are stored on the back-end as two different data types.

Try wrapping the whole LEFT formula in a VALUE function. That should clear up the most immediate issue.

=VALUE(LEFT(……………….), 4))

Now for the tip: VLOOKUP is terrible. INDEX/MATCH is so much better in so many ways.

=INDEX({Single Column To Pull Over}, MATCH([Column Name]@row, {Single Column To Match In}, 0))

DKazatsky2

Hi @Sam H.,

Give this a try.

=IF(COUNTIFS({Clinic ID}, {Clinic ID} = [Clinic ID]@row, {Start Date}, @cell <= [Invoiced Date]@row, {End Date}, @cell >= [Invoiced Date]@row, {Code}, OR(@cell = "AHHI-1", @cell = "AHNN-1")) > 0, 1, 0)

Hope this helps,

Dave

heyjay

https://community.smartsheet.com/discussion/comment/449724#Comment_449724

@DKazatsky2 - your formula seems incomplete. Im noticing this happening to me more often now. When copying a formula with the '@cell' word, it usually cuts off. I have to paste it twice.

DKazatsky2

Hi @heyjay,

I actually went back in and retyped the missing content. You still don't see it?

Samantha S.

It's not throwing an error anymore but it's not check marking events that are valid like the below. The sale took place on 8/8 and there was a valid tracking code:

=IF(COUNTIFS({Clinic ID}, {Clinic ID} = [Clinic ID]@row, {Start Date}, @cell <= [Invoiced Date]@row, {End Date}, @cell >= [Invoiced Date]@row, {Code}, OR(@cell = "AHHI-1", @cell = "AHNN-1", @cell = "AHNN-3", @cell = "AHN5-1", @cell = "AHN5-4")) > 0, 1, 0)

Paul Newcome

Your 2nd {Range} should actually be "@cell".

=IF(COUNTIFS({Clinic ID}, @cell = [Clinic ID]@row, {Start Date}, @cell <= [Invoiced Date]@row, {End Date}, @cell >= [Invoiced Date]@row, {Code}, OR(@cell = "AHHI-1", @cell = "AHNN-1", @cell = "AHNN-3", @cell = "AHN5-1", @cell = "AHN5-4")) > 0, 1, 0)

Samantha S.

Thanks Paul. I made that change, but the box is still not checking appropriately.

Paul Newcome

How are the Clinic IDs getting populated in each of the sheets?

Samantha S.

One is through a formula and the other is manual entry.

Paul Newcome

What is the formula?

Samantha S.

Here is the formula for the sheet I'm trying adding this new formula to:

And it is referencing this sheet below. I had to do it this way for the VLOOKUP because the Clinic ID column comes before the Clinic Name on the reference sheet and I can't change it.

Paul Newcome

Ok. 2 things… I see the issue here, and I have a suggestion / tip for other headaches.

The issue here… When you manually type in a number, it is stored as numerical data. When you use a LEFT function, it is stored as text data. While they may look the same (aside from left vs right justified in the cell which is what led me here), they are stored on the back-end as two different data types.

Try wrapping the whole LEFT formula in a VALUE function. That should clear up the most immediate issue.

=VALUE(LEFT(……………….), 4))

Now for the tip: VLOOKUP is terrible. INDEX/MATCH is so much better in so many ways.

=INDEX({Single Column To Pull Over}, MATCH([Column Name]@row, {Single Column To Match In}, 0))

Samantha S.

Ah ok. So I added the VALUE and the boxes then checked correctly for Valid Sale?, but whenever I try INDEX/MATCH I mess something up and it doesn't pull the correct data. VA Fishersville is the only one now showing the correct Clinic ID.

Paul Newcome

Your parenthesis are just a little out of place there towards the end.

You have:

{Clinic Name2}), 0), 4))

Try this:

{Clinic Name2}, 0)), 4))

Samantha S.

Wow lol. Thank you!

Paul Newcome

Happy to help. 👍️