0

I'm struggling to write an excel formula using CountIfs that meet a certain criteria:

Criteria:

  1. Filter by the week #
  2. The line state must be "Down"
  3. The duration must exceed 15 minutes
  4. Must not count an incident twice if it happened in the same day

This is the formula I have so far:

  =COUNTIFS('Raw Data'!A3:$A$1000000,'Tracker'!$B42,'Raw Data'!C3:C1000000, "Down", 'Raw Data'!E3:E1000000, ">=0.010417")

where 'Tracker'!B42 is the Week# I am searching for, and 0.010417 is the time conversion to 15 minutes.

This formula does work, but is counting events that happen in the same day twice. I am unsure how to filter for this.

Here is a reference table from my spreadsheet to help explain further: Reference Table

For example, I want this formula to only count "Filler Capper" Reason on the 10/6 as one event instead of two. Any suggestions on how to do this?

Improve this question

1 Answer 1

Reset to default
0

I think the formula for counting the number of distinct dates based on 4 criteria is pretty ugly. I almost solved it but gave up. Using a pivot table is so much easier IMO. I don't know if this is reasonable solution for what you need, but you could easily summarize each week this way.

First I created a DateValue column, which is a numeric value of the date.

Cell C2 formula:

    =DATEVALUE(MONTH(B2) & "/" & DAY(B2) & "/" & YEAR(B2))

Then I created a Duration_Flag column.

Cell G2 formula:

    =IF(F2>=$J$1,"Yes","No")

Input the minimum duration in cell J1, then create a pivot table on columns A:G. While creating this pivot, select the "add this data to the data model" check box. This will allow you to count distinct.

After creating your pivot such as the design below, right-click on the DateValue cell (what you want to count), and then go to Value Field Settings. Scroll down to choose "distinct count".

enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.