IF AND In Between Formula
I'm attempting to mark whether a sale is valid based on the following criteria:
- Clinic must have had an AHHI-1 or AHNN-1 tracking code and
- 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
Best Answer
-
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))
Answers
-
@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.
...
-
Hi @heyjay,
I actually went back in and retyped the missing content. You still don't see it?
-
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)
-
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)
-
Thanks Paul. I made that change, but the box is still not checking appropriately.
-
How are the Clinic IDs getting populated in each of the sheets?
-
One is through a formula and the other is manual entry.
-
What is the formula?
-
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.
-
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))
-
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.
-
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))
-
Wow lol. Thank you!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!