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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!