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")

Answers

  • Bassam Khalil
    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):


    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • 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
    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.

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!