LOOKUP Formula based on result between date range

Hello,

I've tried to create a LOOKUP formula that will provide the "Approved Value" if the "Date Certified" calls between the period stipulated in "Period Start" and "Period End," without much success.

Based on the screen-grab, only two values should be returned if the formula is successful. (I've manually entered these to show the values.).

Assistance would be appreciated.

Thanks

Richard.

Find more posts tagged with

Construction

Formulas

Comments

Paul Newcome

Are you trying to display both values or add them together or just count that there are two values?

Richard Heath

Hi Paul,

I'm looking to only display one value in the Parent Row being the Approved Claim value.

I've updated the Screen Shot to attempt to clarify things. I am hoping to include a formula that looks at the Date Certified column, determine if the Date Certified value falls between the Period Start and Period End values and if it does, return the adjacent cell in the Approved Claim column to the Parent cell as highlighted in Yellow.

As an example, the $2,000 approved on the 15th of May 2019 occurred during the defined period, therefore the value ($2,000) should appear under “Approved This Period.” (Yellow Box). I've suggested where else the FORMULA should provide a result (Yellow Boxes)

Thanks for your help.

Regards,

Richard.

Updated Capture.JPG

Paul Newcome

Ok. I am understanding much better now. You want the blue rows to display the value (if any) of the white rows underneath of it that falls within the specified period.

This leads me to two more questions...

1. Are the white rows actually indented below the blue rows as an actual parent/child relationship?

2. What if there is more than one value in any given section that meets the given date criteria? For example... In your first section ($11,000.00), what if the $1,000.00 row also happened to fall within your date range? Would you want to display $2,000.00 and $1,000.00 or would you want $3,000.00 displayed?

Richard Heath

Hi Paul,

In response to you queries;

1. Yes they are indented - I've added a new capture showing the entire screen.

2. The chance that two values were met the criteria for the month is low, but if it did happen, they should probably add together.

I have managed to create the formula in Excel, but being an Array Formula, it doesn't appear to carry over to Smartsheet. I've successfully changed dates in the Period Start/End fields which then correctly alters the "Approved this Period" field.

={INDEX(F$3:F$5,MATCH(1,IF(G3:G5>=J$2,IF(G3:G5<=K$2,1)),0))}

Excel Version.JPG

Paul Newcome

Ok. To account for the off chance that two lines would overlap in dates, we will use a basic SUMIFS instead of an INDEX/MATCH.

Try something along the lines of...

=SUMIFS(CHILDREN([Approved Claim]@row), CHILDREN([Date Certified]@row), AND(@cell >= [Period Start]$1, @cell <= [Period End]$1))

Richard Heath

Amazing. Thanks for that.

I really appreciate your assistance with this one.

Paul Newcome

Happy to help!

Sign in to join the conversation!

Quick Links

Trending discussions

Getting error message in approval email
Hey, Is anyone else facing this issue in the approval notifications sent through smartsheet
Add Package Count Based on Customer
I have a sheet with a list of customers in one column, and then the following columns are City, Monday, Tuesday, Wednesday, Thursday, Friday. I need help with a formula that I can put in my sheet summary so that if the customer column says Staples (It can say this in multiple rows) that it will tell me the total package…
Automation not triggering all of the time
Hi Smartsheet community, I've always tried to answer questions but this is my first time posting. So I have formula =IF([Project Status]@row = "Complete", 1, 0) set up in the "Complete Check - Hide column" to check the box when the project status is marked complete. The project status column is tied to several other sheets…