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

• ✭✭✭✭✭✭

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

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.

• ✭✭✭✭✭✭

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.

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!