SOLVED - formula that will calculate a metric (on time delivery) with criteria

[excellerator02]

Hey,

I am trying to create a formula that will calculate a metric (on time delivery). I want the formula to count cells based upon some criteria. The criteria is if the date received is greater than 2 or less than -5 of the ETA and within the last 30 days of the received date in the "RCVD date" area.

I was able to create a sumifs formula that almost did what I wanted but it was summing all of the days late column when I just need it to be counted.

[bebo021999]

Are you looking for 75%?
Why and how your desired result come? Explain and we will help you to produce a formula.

[excellerator02]

Yes the correct answer on the sample sheet is 75%.

Why: I want to know what percent of deliveries are being delivered within a specific time window. The sample is just a few lines but we typically have 6-900 lines on a job.

How: If a delivery is outside a window (5 days early to 2 days late) within the last month I want that line to be counted. I also want the output to be a percentage of total lines that were delivered within the last month. Ex 10 deliveries within last 30 days and 5 were on time. Should produce 50%

[AliGW]

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. As you are new, I have done it for you today.)

[bebo021999]

In this sample, how 75% be calculated? is it 3 rows/4 rows?
what are the 3 rows and 4 rows?
And what is last month? from 30-Apr (cell E1) or from today's date?

[excellerator02]

Yes the 75% is calculated by 3 rows out of 4 rows.

The 3 rows are rows 3, 4, and 5.

The 3 rows are validated by being within the last 30 days of cell E1, being greater than 5 or less than -2.

The 4 rows are validated by being with the last 30 days of cell E1.

The last month is cell E1 or 30-Apr. This could change I just need to be able to lookup data for a specific month of the year. Jan-Feb-March-etc.

[bebo021999]

To count # dates within last 30 days:
COUNTIFS($C$2:$C$6,">="&$E$1-30)
return 3 (Not 4, row 1 (9-Mar) and 6 (14-Feb) are out of last 30 days)

To count # dates within last 30 days, and >5 or <-2:
SUMPRODUCT(($C$2:$C$6>=$E$1-30)*(($B$2:$B$6>5)+($B$2:$B$6<-2)))

to do average:

=SUMPRODUCT(($C$2:$C$6>=$E$1-30)*(($B$2:$B$6>5)+($B$2:$B$6<-2)))/COUNTIFS($C$2:$C$6,">="&$E$1-30)

= 100%

Is it are you looking for?

[excellerator02]

Bebo,

That works excellent! Thank you so much. I am going to try and apply it to the actual document and will follow back up.

[excellerator02]

Bebo,

When I applied it to the larger spreadsheet I would like 1 more change.

When the date section is used its greater than or equal to the date entered - 30. Is there anyway to make it only look at dates from April or June? Or have it look at the date entered -30 but not more than +30?

In the below images using a date of 4/1/21 would allow me to lookup data for March. Since the formula is open-ended (greater than or equal to) it also adds in the April data. I would like the OTD to be 0% since there was 1 delivery in March and it was late.

12.JPG

1212.JPG

[bebo021999]

Try again with new criteria for upper limitation:
=SUMPRODUCT(($C$2:$C$6>=$E$1-30)*($C$2:$C$6<=$E$1)*(($B$2:$B$6>5)+($B$2:$B$6<-2)))/COUNTIFS($C$2:$C$6,">="&$E$1-30,$C$2:$C$6,"<="&$E$1)

[excellerator02]

Bebo,

If I could I would give you double rep I would. Thanks so much for coming back and helping me out!