Best Of

Hi @Robert S

If you're looking into another sheet, we'll need to use a Cross Sheet formula to return the number in your Bake Time column.

Assuming that the PartNumber is unique, we can use an INDEX(MATCH combination formula to look into your second sheet and bring back the time associated with the matching PartNumber. See: Formula combinations for cross sheet references

So something like this:

=INDEX({Bake Time Column}, MATCH([Part Number]@row, {Part Number Column}, 0))

Then we can use this in your IF statement to say that if there's a date, add the days that the cross-sheet formula returns:

=IF(ISDATE([Date In]@row), [Date In]@row + IFERROR(INDEX({Bake Time Column}, MATCH([Part Number]@row, {Part Number Column}, 0)), " - No Match"))

Let me know if that makes sense and works for you!

Cheers,

Genevieve

Thank you all for the help on this, we ended up with the following formula:

=IF(OR(Sit@row = "NA", Sit@row = "", Removed@row = 1), 0, COUNTIFS([School Name]:[School Name], [School Name]@row, Sit:Sit, Sit@row, Removed:Removed, Removed@row = 0))

Adding the OR statement so if it was NA or <Blank> would force the 0.

To account for the possibility of more than one transaction for a person on the same day, I would suggest inserting an auto-number column with no special formatting. Then you would use a COUNTIFS like so:

=COUNTIFS([Initials + Month]:[Initials + Month], @cell = [Initials + Month]@row, Date:Date, @cell<= Date@row, AutoAuto, @cell<= Auto@row)

The column using the DATEONLY function is storing the original format on the back-end which is the format used when generating the PDF.

You will need a text/number column with a formula that outputs a text string that just looks like the date format you want.

=DAY([Created Date]@row) + "-" + IF(MONTH([Created Date]@row) = 1, "Jan", IF(MONTH([Created Date]@row) = 2, "Feb", IF(MONTH([Created Date]@row) = 3, "Mar", IF(MONTH([Created Date]@row) = 4, "Apr", IF(MONTH([Created Date]@row) = 5, "May", IF(MONTH([Created Date]@row) = 6, "Jun", IF(MONTH([Created Date]@row) = 7, "Jul", IF(MONTH([Created Date]@row) = 8, "Aug", IF(MONTH([Created Date]@row) = 9, "Sep", IF(MONTH([Created Date]@row) = 10, "Oct", IF(MONTH([Created Date]@row) = 11, "Nov", "Dec"))))))))))) + "-" + YEAR([Date Created]@row)

Hi @Ridac

I hope you're well and safe!

You could have an automated Alert trigger when the form is submitted, which can also include the information from column b.

Make sense?

Would that work/help?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

You would first need a helper column with a formula that outputs the dollar amount for a single month.

=(MONTH([End Date]@row) - MONTH([Start Date]@row)) + ((YEAR([End Date]@row) - YEAR([Start Date]@row)) * 12) + 1

Then you would use a SUMIFS to get the total amount for a single month from every row like so:

=SUMIFS([Helper Column]:[Helper Column], [Start Date]:[Start Date], AND(IFERROR(MONTH(@cell), 0) <= 6, IFERROR(YEAR(@cell), 0) >= 2023), [End Date]:[End Date], AND(IFERROR(MONTH(@cell), 0) >= 6, IFERROR(YEAR(@cell), 0) >= 2023))

The above is for June 2023. Adjust the 6s for the month you want to sum for and the 2023s for the years.