Summing multiple countifs statements?

I'm trying to track how many days an item was out of stock over a rolling period of time. The formula I am currently using is below. The issue I'm running into is when an item is reported out of stock several times in one day it is reporting out a false number. How can I keep a running total but if it is reported out of stock on the same day that only counts as one time?

Tracker-Open Range 2 refers to the item, Tracker- Open Range 6 refers to the items status and tracker open Range 5 is the number of days

=COUNTIFS({Tracker- Open Range 2}, Item@row, {Tracker- Open Range 6}, "Out of Stock")

Find more posts tagged with

Accepted answers

All comments

Bassam Khalil

Hi @Jacob.Lassiter,

Based on your description, I have designed two sheets and created the following formula. Please check to see if it meets your needs.

=COUNTIFS({tracker open Range 5}, AND(@cell <= $To$1, @cell >= $From$1), {Tracker- Open Range 2}, Item@row, {Tracker- Open Range 6}, @cell = "Out of Stock")

You must drag the formula through all item rows in the summary sheet (T1),For example, in row number 2, it will be as follows:

=COUNTIFS({tracker open Range 5}, AND(@cell <= $To$1, @cell >= $From$1), {Tracker- Open Range 2}, Item@row, {Tracker- Open Range 6}, @cell = "Out of Stock")

Here is a screenshot of the summary sheet T1:

and below is a screenshot of the database sheet (T2):

Jacob.Lassiter

Thank you @Bassam Khalil!

Based on the database sheet I would like for the summary sheet T1 to look something like this since Item 1 was only out of stock on three separate days.

Bassam Khalil

Hi @Jacob.Lassiter,

ok here is the solution:

1- Create helper column in Database sheet T2 call it [Unique ] In this column, you'll use a formula to check if the current item is the first occurrence of that item in the list. If so, it'll mark it as unique (e.g., with a 1). If not, it will mark it as a duplicate (e.g., with a 0). the following is the formula to check ( note you must drag the cell to fill the formula in all rows and the number of rows will be change automatically:

=IF(COUNTIFS(Item$1:Item1, Item@row, Date$1:Date1, Date@row) > 1, 0, 1)

and here is a screenshot shows the result

2- in the summary sheet T1 use the following formula to count the unique items as per your criteria:

=SUMIFS({Unique}, {tracker open Range 5}, AND(@cell <= $To$1, @cell >= $From$1), {Tracker- Open Range 2}, Item@row, {Tracker- Open Range 6}, @cell = "Out of Stock")

here is a screenshot shows the result.

i hope this will solve your issue.

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.