count if value is between a date range (reference between 2 sheets)

Hi team

i have 2 sheets for material inventory. the first is to record witch equipment is ship out for repair and the other is the total of equipment available.

the first is set as follow:

equipment type, date out, date return, quantity (negative number)

the other one is as follow: each row is for a single date with the total of cage available

i'm looking for a fomula that can calculate the number of cage out between the range date of the first sheet to adjust the total per day on the second sheet (from the 25-sep-2025 to the 4-oct-2025 the total shoudl be 130 and return to 160 on the 5-oct-2025)

thank you for your help

Find more posts tagged with

Accepted answers

DKazatsky2

Hi @Julien Lopez,

See if this works for you.

=IF(SUMIFS({Quantity Out}, {Date Out}, <=Date@row , {Date Return}, >=Date@row ) <> 0, 160 + SUMIFS({Quantity Out}, {Date Out}, <=Date@row , {Date Return}, >=Date@row ), 160)

This formula goes in in the second sheet, in "Cage - Dog" column. All cross sheet references are to the first sheet.

NOTE: If you change the amount of cages, the formula needs to be updated - where it says 160 should always be the base inventory number.

Hope this helps,

Dave

All comments

DKazatsky2

Hi @Julien Lopez,

See if this works for you.

=IF(SUMIFS({Quantity Out}, {Date Out}, <=Date@row , {Date Return}, >=Date@row ) <> 0, 160 + SUMIFS({Quantity Out}, {Date Out}, <=Date@row , {Date Return}, >=Date@row ), 160)

This formula goes in in the second sheet, in "Cage - Dog" column. All cross sheet references are to the first sheet.

NOTE: If you change the amount of cages, the formula needs to be updated - where it says 160 should always be the base inventory number.

Hope this helps,

Dave

Julien Lopez

Hi @DKazatsky2

it works

thank you very much

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.